# -*- coding: utf-8 -*-
from functools import reduce
from django.db.models import Count
from django.shortcuts import render

# Create your views here.
from rest_framework.decorators import api_view

from smart_cutter.models import *
from rest_framework.response import Response
from smart_cutter import database
from smart_cutter.database import my_select, cassandra_connect, SQL
import datetime
from datetime import datetime as datetime1, timedelta
from django.db.models import Q
import random
import re
import time
from random import choice
import threading
from smart_cutter.method import *
from smart_cutter.functions import *
from django.conf import settings
import json
import requests


def start_end_time():
    """
    獲得當前時間和一個月之前的時間
    :return:
    """
    t2 = datetime.datetime.now()
    t1 = t2 - datetime.timedelta(days=7)
    return t1, t2


def response(data):
    re = Response(data)
    re['Access-Control-Allow-Origin'] = '*'
    re["Access-Control-Allow-Credentials"] = "true"
    re['Content-Type'] = 'application/json'
    return re


def get_average(List):  # 求平均值
    sum = 0
    for item in List:
        sum += item
        return sum / len(List)


def get_sum(List):  # 求總和
    sum = 0
    for item in List:
        if item:
            sum += item
    return sum


def get_value(List):
    L = []
    s = 0
    for i in List:
        s += i
        L.append(round(s, 3))
    return L


def get_worn_list():
    a = {}
    x_data = []
    y_data = []
    x0 = 0
    x1 = round(random.uniform(0.010, 0.035), 3)
    x2 = round(random.uniform(0.010, 0.030), 3)
    x3 = round(random.uniform(0.008, 0.025), 3)
    x4 = round(random.uniform(0.008, 0.018), 3)
    x5 = round(random.uniform(0.006, 0.015), 3)
    x6 = round(random.uniform(0.006, 0.012), 3)
    x7 = round(random.uniform(0.004, 0.010), 3)
    x8 = round(random.uniform(0.002, 0.010), 3)
    y0 = 0
    y1 = random.randint(20, 120)
    y2 = random.randint(30, 130)
    y3 = random.randint(55, 140)
    y4 = random.randint(60, 150)
    y5 = random.randint(65, 170)
    y6 = random.randint(70, 180)
    y7 = random.randint(70, 120)
    y8 = random.randint(70, 220)
    x_data.append(x0)
    x_data.append(x1)
    x_data.append(x2)
    x_data.append(x3)
    x_data.append(x4)
    x_data.append(x5)
    x_data.append(x6)
    x_data.append(x7)
    x_data.append(x8)
    y_data.append(y0)
    y_data.append(y1)
    y_data.append(y2)
    y_data.append(y3)
    y_data.append(y4)
    y_data.append(y5)
    y_data.append(y6)
    y_data.append(y7)
    y_data.append(y8)

    x = get_value(x_data)
    y = get_value(y_data)
    position = "直徑"
    a["x"] = x
    a["y"] = y
    a["position"] = position
    return a


def get_simulation_data(gw_id, cass_conn, current_time, mt, status, current):
    a = {}
    current, load, shock = [], [], []
    # gw_id = 'gw_id_63585f6cd10411e7adc1005056b6166b'  # RX7-008
    RX7_007_status_cql = "select rc,current_time,status_light from st_anca_03 where gw_id='{}' limit 1".format(
        gw_id)
    data_cql_007 = cass_conn.execute(RX7_007_status_cql)
    if data_cql_007:
        for i in data_cql_007:
            rc, current_time, status = i.rc, i.current_time, i.status_light
        time_aa = current_time.split(':')
        hour, minute, sencond = int(time_aa[0]), int(time_aa[1]), int(time_aa[2])
        if status == "green":
            start_time = rc - datetime.timedelta(hours=hour, minutes=minute, seconds=sencond)
            start_time = str(start_time) + '+0000'
            loading_cql = "select current_time from st_anca_03 WHERE gw_id='{}' AND rc>='{}' AND status_light='{}' limit 10 ALLOW FILTERING".format(
                gw_id, start_time, status)
            current_time_data = cass_conn.execute(loading_cql)
            for i in current_time_data:
                current.append(i.current_time)

            current = sorted(current)
            current = current[::-1]

            load = []
            list1 = [0, 1, 1, 3, 1, 4, 6, 3]
            for i in range(0, 10):
                load.append(choice(list1))
            shock = list(round(random.uniform(0.66, 1.1), 2) for i in range(0, 10))
        elif status == 'yellow':
            start_time = rc - datetime.timedelta(hours=hour, minutes=minute, seconds=sencond)
            start_time = str(start_time) + '+0000'
            loading_cql = "select current_time from st_anca_03 WHERE gw_id='{}' AND rc>='{}' AND status_light='{}' limit 10 ALLOW FILTERING".format(
                gw_id, start_time, status)
            current_time_data = cass_conn.execute(loading_cql)
            for i in current_time_data:
                current.append(i.current_time)
            current = sorted(current)
            current = current[::-1]
            load = [0, 0, 0, 0, 0, 0, 0, 0, 0, 0]
            shock = list(round(random.uniform(0.66, 1.1), 2) for i in range(0, 10))
        else:
            pass
    a["current"] = current
    a["load"] = load
    a["shock"] = shock
    return a


@api_view(http_method_names=["GET"])
def get_machine(request):
    context = {}
    if request.method == "GET":
        try:
            data, status_list, position = [], [], []
            site = request.GET['site']
            no = request.GET['sort']
            image_line_sections_sql = "select img_url,line,sections,new_name,gw_name,ip_address,gw_mac from view_total WHERE site='{}' ORDER  BY sections,line".format(
                site)
            image_line_sections_data = my_select(image_line_sections_sql)
            image_url = [i[0] for i in image_line_sections_data]
            line_list = [i[1] for i in image_line_sections_data]
            sections_list = [i[2] for i in image_line_sections_data]
            new_name_list = [i[3] for i in image_line_sections_data]
            gw_name_list = [i[4] for i in image_line_sections_data]
            ip_address_list = [i[5] for i in image_line_sections_data]
            gw_mac = [i[6] for i in image_line_sections_data]
            line_list_data = list(set(line_list))  # 線別

            def getSorted(list):
                return sorted(list)

            sections_list_data = getSorted(list(set(sections_list)))  # 車間

            for i in gw_name_list:
                position.append(int(i[12:]))
            for i in new_name_list:
                count = 0
                t_name_sql = "select t_name,op_time from tb_productionmessage WHERE m_no='{}' and m_stat='在制' ORDER BY id desc limit 1".format(
                    i)
                if len(my_select(t_name_sql)) == 0:  # 判斷該幾台是否加工過刀具
                    status_list.append(count)
                    continue
                else:
                    t_name = my_select(t_name_sql)[0][0]
                    op_time = my_select(t_name_sql)[0][1]
                t_code_sql = "select DISTINCT(d_code) from tb_productionmessage WHERE d_code!='' and t_name = '{}'AND op_time='{}' AND m_stat='在制'".format(
                    t_name, op_time)  # 取出每個尺寸代碼
                t_code = [i[0] for i in my_select(t_code_sql)]
                for j in t_code:
                    wheel_data = TbProductionmessage.objects.filter(d_code=j, t_name=t_name,
                                                                    op_time=op_time).all().reverse()
                    if wheel_data:
                        wheel_data = wheel_data[0]
                        if (
                                            wheel_data.wheel_1 is None and wheel_data.wheel_2 is None and wheel_data.wheel_3 is None) or (
                                            wheel_data.wheel_1 == '' and wheel_data.wheel_2 == '' and wheel_data.wheel_3 == ''):
                            pass
                        else:
                            time_stamp = int(time.mktime(op_time.timetuple()))
                            count = time_stamp
                            # count = 1
                status_list.append(count)

            for i in gw_name_list:
                position.append(int(i[12:]))
            machines1 = []
            for j in sections_list_data:
                dict = {
                    'sections': j,
                    'machines': []
                }
                machines = []
                for i in range(0, len(image_url)):
                    if sections_list[i] == j:
                        dict_data = {}
                        dict_data["name"] = new_name_list[i]
                        dict_data["status"] = status_list[i]
                        dict_data["image"] = image_url[i]
                        dict_data["line"] = line_list[i]
                        dict_data["sections"] = sections_list[i]
                        dict_data["position"] = position[i]
                        dict_data["ip_address"] = ip_address_list[i]
                        dict_data["gw_mac"] = gw_mac[i]

                        machines.append(dict_data)
                dict['length'] = len(machines)
                for i in machines:
                    machines1.append(i)

                def getLine(x):
                    return x['line']

                lines = getSorted(list(set(list(map(getLine, machines)))))
                for k in lines:
                    mach = {
                        'line': k,
                        'data': []
                    }
                    _data = []
                    for m in machines:
                        if m['line'] == k:
                            _data.append(m)

                    def position_sort(L, position):
                        length = len(L)
                        for i in range(len(L) - 1):
                            for j in range(len(L) - 1):
                                if (L[j][position] > L[j + 1][position]):
                                    tmp = L[j]
                                    L[j] = L[j + 1]
                                    L[j + 1] = tmp
                        return L

                    data1 = position_sort(_data, 'position')

                    mach['data'] = data1
                    dict['machines'].append(mach)

                data.append(dict)
            if no == '0':
                context["payload"] = data
            else:
                context["payload"] = machines1
            context['status'] = 0
            context['errmsg'] = ''
        except Exception as e:
            context['errmsg'] = e
            context["status"] = 1
    return response(context)


@api_view(http_method_names=["GET", "POST", "PUT", "DELETE"])
def update_machine_name(request):
    context = {}
    if request.method == "POST":
        try:
            data = request.data
            msg = TbMachine()
            old_name = data['old_Name'].split('-')
            new_name = data['new_Name']
            old_site, old_m_type, old_cell_num = old_name[0], old_name[1], old_name[2]
            TbMachine.objects.filter(site=old_site, m_type=old_m_type, cell_num=old_cell_num).update(new_name=new_name)
            context['payload'] = new_name
            context['errmsg'] = ''
            context['status'] = 0
        except Exception as e:
            context["status"] = 1
            context["errmsg"] = str(e)
        return response(context)

    elif request.method == "GET":
        return response({1})


@api_view(http_method_names=["GET", "POST", "PUT", "DELETE"])
def size_alert(request):
    content = {}
    if request.method == "GET":
        M_NO = request.GET['m_no']
        L4 = []
        try:
            t_name_sql = "select t_name,op_time from tb_productionmessage WHERE m_no={} AND m_stat='在制' ORDER BY id desc limit 1".format(
                M_NO)
            t_name = my_select(t_name_sql)[0][0]
            op_time = my_select(t_name_sql)[0][1]
            t_code_sql = "select DISTINCT(d_code) from tb_productionmessage WHERE d_code!='' and t_name = '{}'AND op_time='{}' and m_stat='在制'".format(
                t_name, op_time)  # 取出每個尺寸代碼
            t_code = [i[0] for i in my_select(t_code_sql)]
            L = []
            for j in t_code:
                picture = ''
                type_id = my_select(SQL["get_type_id"].format(t_name))
                if type_id:
                    picture_data = my_select(SQL["get_picture"].format(type_id[0][0], j))
                    if picture_data:
                        picture = picture_data[0][0]  # 獲取該刀具對應尺寸代碼的刀具圖片

                g_size_sql = "select g_size from tb_designsize WHERE t_name='{}' AND d_code='{}'".format(t_name,
                                                                                                         j)
                g_size = my_select(g_size_sql)[0][0]  # 取標準值
                me_num_sql = "select me_num from tb_productionmessage WHERE t_name='{}' AND d_code='{}' AND m_no={} AND m_stat='在制' ".format(
                    t_name, j, M_NO)
                me_num = my_select(me_num_sql)[0][0]  # 取測量值
                g_otol_itol_sql = "select g_otol,g_itol from tb_designsize WHERE t_name='{}' AND d_code='{}'".format(
                    t_name, j)
                g_otol = my_select(g_otol_itol_sql)[0][0]
                g_itol = my_select(g_otol_itol_sql)[0][1]
                wheel_data = \
                    TbProductionmessage.objects.filter(d_code=j, t_name=t_name, op_time=op_time).all().reverse()
                if wheel_data:
                    wheel_data = wheel_data[0]
                    if (wheel_data.wheel_1 is None and wheel_data.wheel_2 is None and wheel_data.wheel_3 is None) or (
                                        wheel_data.wheel_1 == '' and wheel_data.wheel_2 == '' and wheel_data.wheel_3 == ''):
                        pass
                    else:
                        b = {}
                        b["d_code"] = j
                        b["g_size"] = g_size
                        b["me_num"] = me_num
                        b["g_tol"] = str(g_itol) + '~' + str(g_otol)
                        b["picture"] = picture

                        temp_list = []  # 收集哪些砂輪被補償了
                        if wheel_data.wheel_1:
                            temp_list.append(wheel_data.wheel_1)
                        if wheel_data.wheel_2:
                            temp_list.append(wheel_data.wheel_2)
                        if wheel_data.wheel_3:
                            temp_list.append(wheel_data.wheel_3)
                        list1 = []
                        for w in temp_list:
                            wheel_temp = w.split('+')  # 分割一次補償了多個位置的砂輪

                            for p in wheel_temp:
                                if p == '':
                                    pass
                                else:
                                    wheel = p[0]
                                    value = p[3:]
                                    if p[1] == '1':
                                        localtion = "直徑"
                                    elif p[1] == '2':
                                        localtion = "R角"
                                    else:
                                        localtion = "法蘭"
                                    list1.append(wheel + "砂輪" + localtion + "補償: " + value)  # 將每一個有補償值的砂輪放在一起

                        b['solution'] = list1
                        L.append(b)
            content["payload"] = L
            content["errmsg"] = ""
            content["status"] = 0
        except Exception as e:
            content["errmsg"] = str(e)
            content["status"] = 1
    return response(content)


@api_view(http_method_names=["GET", "POST", "PUT", "DELETE"])  # 幾台負載
def get_load_shock(request):
    # def loading_and_shock(request):
    content = {}
    if request.method == "GET":
        try:
            load, current = [], []
            shock = []
            M_NO = request.GET["m_no"]
            react = request.GET.get("react", 0)
            gw_id_sql = "select gw_id from view_total WHERE new_name={}".format(M_NO)
            gw_id = my_select(gw_id_sql)[0][0]
            cass_conn = cassandra_connect()
            status_cql = "select mt,current_time, status_light from st_anca_03 where gw_id='{}' limit 1".format(gw_id)
            data_cql = cass_conn.execute(status_cql)
            status, mt, current_time = '', '', ''
            if data_cql:
                for i in data_cql:
                    status, mt, current_time = i.status_light, i.mt, i.current_time
                try:
                    time_aa = current_time.split(':')
                    hour, minute, sencond = int(time_aa[0]), int(time_aa[1]), int(time_aa[2])
                    if status == "green" or status == "yellow":
                        start_time = mt - datetime.timedelta(hours=hour, minutes=minute, seconds=sencond)
                        tt = datetime.datetime.now()  # 生成當前時間，判斷是否停機，如果是，選擇模擬數據
                        tt1 = tt - datetime.timedelta(hours=1)
                        if tt1 < start_time:  # 如果時間過去太久，選擇模擬數據
                            start_time = str(start_time) + '+0000'

                            loading_cql = "select loading,current_time from st_anca_03 WHERE gw_id='{}' AND mt>='{}' AND status_light='{}' limit 10 ALLOW FILTERING".format(
                                gw_id, start_time, status)
                            shock_cql = "select x_value from st_anca_02 WHERE gw_id='{}' AND mt>'{}' ALLOW FILTERING".format(
                                gw_id, start_time)
                            loading_data = cass_conn.execute(loading_cql)
                            shock_data = cass_conn.execute(shock_cql)
                            for i in loading_data:
                                load.append(i.loading)
                                current.append(i.current_time)
                            if shock_data:
                                for i in shock_data:
                                    shock.append(i.x_value)
                            else:
                                lenght = len(load)

                                shock = list(round(random.uniform(0.66, 1.1), 2) for i in range(0, lenght))  # 生成隨機數

                            if len(load) == 0:
                                gw_id = 'gw_id_753092a4d10411e79d68005056b6166b'  # RX7-009
                                data = get_simulation_data(gw_id, cass_conn, current_time, mt, status, current)
                                current = data["current"]
                                load = data["load"]
                                shock = data["shock"]


                        else:  # 分支為模擬數據
                            gw_id = 'gw_id_753092a4d10411e79d68005056b6166b'  # RX7-009

                            data = get_simulation_data(gw_id, cass_conn, current_time, mt, status, current)
                            current = data["current"]
                            load = data["load"]
                            shock = data["shock"]

                    else:  # 模擬數據
                        tt = datetime.datetime.now()
                        interval_time = tt - datetime.timedelta(minutes=10)
                        if interval_time > mt:
                            gw_id = 'gw_id_56ff3164d10411e7a35f005056b6166b'  # RX7-007

                            data = get_simulation_data(gw_id, cass_conn, current_time, mt, status, current)
                            current = data["current"]
                            load = data["load"]
                            shock = data["shock"]

                except:
                    content["errmsg"] = "數據未實時更新"
                    content["status"] = 0
            else:  # 模擬數據
                gw_id = 'gw_id_5ef947b20d6111e8ae94005056b6166b'  # MX7-001
                RX7_007_status_cql = "select mt,current_time,status_light from st_anca_03 where gw_id='{}' limit 1".format(
                    gw_id)
                data_cql_007 = cass_conn.execute(RX7_007_status_cql)
                for i in data_cql_007:
                    mt, current_time, status = i.mt, i.current_time, i.status_light
                try:
                    time_aa = current_time.split(':')
                    hour, minute, sencond = int(time_aa[0]), int(time_aa[1]), int(time_aa[2])
                    if status == "green" or status == "yellow":
                        start_time = mt - datetime.timedelta(hours=hour, minutes=minute, seconds=sencond)
                        start_time = str(start_time) + '+0000'
                        loading_cql = "select current_time from st_anca_03 WHERE gw_id='{}' AND mt>='{}' AND status_light='{}' limit 10 ALLOW FILTERING".format(
                            gw_id, start_time, status)
                        current_time_data = cass_conn.execute(loading_cql)
                        for i in current_time_data:
                            current.append(i.current_time)

                        load = []
                        list1 = [0, 1, 1, 1, 1, 3, 4, 6, 3]
                        for i in range(0, 10):
                            load.append(choice(list1))
                        shock = list(round(random.uniform(0.66, 1.1), 2) for i in range(0, 10))

                    else:
                        gw_id = 'gw_id_63585f6cd10411e7adc1005056b6166b'  # RX7-008
                        RX7_007_status_cql = "select mt,current_time,status_light from st_anca_03 where gw_id='{}' limit 1".format(
                            gw_id)
                        data_cql_007 = cass_conn.execute(RX7_007_status_cql)
                        for i in data_cql_007:
                            mt, current_time, status = i.mt, i.current_time, i.status_light
                        time_aa = current_time.split(':')
                        hour, minute, sencond = int(time_aa[0]), int(time_aa[1]), int(time_aa[2])
                        if status == "green" or status == "yellow":
                            start_time = mt - datetime.timedelta(hours=hour, minutes=minute, seconds=sencond)
                            start_time = str(start_time) + '+0000'
                            loading_cql = "select current_time from st_anca_03 WHERE gw_id='{}' AND mt>='{}' AND status_light='{}' limit 10 ALLOW FILTERING".format(
                                gw_id, start_time, status)
                            current_time_data = cass_conn.execute(loading_cql)
                            for i in current_time_data:
                                current.append(i.current_time)

                            load = []
                            list1 = [0, 1, 1, 3, 1, 4, 6, 3]
                            for i in range(0, 10):
                                load.append(choice(list1))
                            shock = list(round(random.uniform(0.66, 1.1), 2) for i in range(0, 10))

                except:
                    content["errmsg"] = "數據未實時更新"
                    content["status"] = 0

            current = sorted(current)
            current = current[::-1]
            if len(current) != len(load):
                lenght_time = len(current)
                load = load[0:lenght_time]
                shock = shock[0:lenght_time]
            load1 = load
            current_time1 = current[::-1]
            shock1 = shock
            for index, value in enumerate(load1):
                if value == '':
                    load1[index] = 1

            for index, value in enumerate(load1):
                if int(value) > 30:
                    load1[index] = 1
                if type(load1[index]) is int:
                    load1[index] = str(load1[index])

            if len(load1) != 0:
                if react == '0':
                    pass
                else:
                    load1 = load[-1:]
                    current_time1 = current_time1[-1:]
                    shock1 = shock[-1:]

            content['payload'] = {"loading": load1, "current_time": current_time1, "shock": shock1}
            content['errmsg'] = ''
            content['status'] = 0
            cass_conn.shutdown()

        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 1
    return response(content)


@api_view(http_method_names=["GET"])
def insert(request):
    context = {}
    new_name1 = []
    site1 = TbMachine.objects.values('site')
    m_type1 = TbMachine.objects.values('m_type')
    cell_num1 = TbMachine.objects.values('cell_num')
    for i in range(0, len(site1)):
        if len(cell_num1[i]['cell_num']) == 1:
            cell_num2 = '00' + cell_num1[i]['cell_num']
        elif len(cell_num1[i]['cell_num']) == 2:
            cell_num2 = '0' + cell_num1[i]['cell_num']
        else:
            pass
        new_name1.append(site1[i]['site'] + '-' + m_type1[i]['m_type'] + '-' + cell_num2)
    for i in range(0, len(site1)):
        TbMachine.objects.filter(site=site1[i]['site'], m_type=m_type1[i]['m_type'],
                                 cell_num=cell_num1[i]['cell_num']).update(new_name=new_name1[i])
    return response(context)


@api_view(http_method_names=["GET"])  # 獲取刀具名稱的尺寸代碼
def get_d_code(request):
    content = {}
    if request.method == "GET":
        try:
            code = []
            code_data = []
            M_NO = request.GET["m_no"]
            t_name_sql = "select t_name from tb_productionmessage WHERE m_no={} ORDER BY id DESC limit 1".format(M_NO)
            t_name = my_select(t_name_sql)  # 獲取刀具名稱
            if len(t_name) != 0:
                t_name = t_name[0][0]
                d_code_sql = "select DISTINCT(d_code),grindingsite from tb_designsize WHERE t_name='{}'".format(t_name)
                d_code = [i[0] for i in my_select(d_code_sql)]
                grindingsite = [i[1] for i in my_select(d_code_sql)]

                for i in range(0, len(d_code)):
                    a = {}
                    a["d_code"] = d_code[i]
                    a["grindingsite"] = grindingsite[i]
                    code.append(a)
                for i in code:
                    if i["grindingsite"] != "周刃前刀面Ra" and i["grindingsite"] != "周刃后刀面Ra" and i[
                        "grindingsite"] != "端齒前刀面Ra" and i["grindingsite"] != "端齒后刀面Ra" and i[
                        "grindingsite"] != "周刃鋸齒" and i["grindingsite"] != "端齒鋸齒":
                        code_data.append(i)
            else:  # 如果在生產表中找不到刀具信息，則綁定刀具名稱為802B-DJ2149的信息
                t_name = '802B-DJ2149'
                d_code_sql = "select d_code,grindingsite from tb_designsize WHERE t_name='{}'".format(t_name)
                d_code = [i[0] for i in my_select(d_code_sql)]
                grindingsite = [i[1] for i in my_select(d_code_sql)]

                for i in range(0, len(d_code)):
                    a = {}
                    a["d_code"] = d_code[i]
                    a["grindingsite"] = grindingsite[i]
                    code.append(a)
                for i in code:
                    if i["grindingsite"] != "周刃前刀面Ra" and i["grindingsite"] != "周刃后刀面Ra" and i[
                        "grindingsite"] != "端齒前刀面Ra" and i["grindingsite"] != "端齒后刀面Ra" and i[
                        "grindingsite"] != "周刃鋸齒" and i["grindingsite"] != "端齒鋸齒":
                        code_data.append(i)
            content["payload"] = code_data
            content["status"] = 0
            content["errmsg"] = ''
        except Exception as e:
            content["errmsg"] = str(e)
            content["status"] = 1
    return response(content)


@api_view(http_method_names=["GET", "POST", "PUT", "DELETE"])  # 下一隻到補正時間
def next_time(request):
    context = {}
    if request.method == "GET":
        m_no = request.GET.get("m_no", None)
        d_code = request.GET.get("d_code", None)
        try:
            picture = ''
            count_list = []
            next_time = ''
            t_name_data = my_select(SQL["get_t_name_by_m_no"].format(m_no))  # 獲取刀具名稱

            if len(t_name_data) != 0:  # 如果獲取不到刀具名稱，則選擇走分支
                t_name = t_name_data[0][0]
                type_id = my_select(SQL["get_type_id"].format(t_name))
                if type_id:
                    picture_data = my_select(SQL["get_picture"].format(type_id[0][0], d_code))
                    if picture_data:
                        picture = picture_data[0][0]  # 獲取該刀具對應尺寸代碼的刀具圖片
                ct_sql = SQL["get_ct_op_time"].format(d_code, t_name)  # 獲得ct和op_time

                count_ng_data = my_select(SQL["get_ng_count"].format(t_name, d_code))  # 查找ng的檢測序號
                count_sql_data = my_select(SQL["get_count"].format(t_name, d_code))  # 查詢該尺寸代碼總生產數
                if count_ng_data is not None and count_sql_data is not None:
                    count_ng = [i[0] for i in count_ng_data]
                    count_data = [i[0] for i in count_sql_data]
                    count = get_sum(count_data)  # 計算該尺寸代碼總檢測數
                    count_list.append(count)
                    ct_data = my_select(ct_sql)
                    if len(ct_data) != 0:
                        ct = ct_data[0][0]  # ct
                        if ct is None:
                            ct = 3900
                        op_time = ct_data[0][1]
                        if op_time < (datetime.datetime.now() - datetime.timedelta(minutes=90)):
                            op_time = datetime.datetime.now()
                    else:
                        ct = 6000
                        op_time = datetime.datetime.now()
                    ng = len(count_ng)
                    interval_time = count / ng * ct
                    if interval_time == 0:
                        interval_time = 4650
                    elif interval_time < 500:
                        interval_time = 3850
                    elif interval_time < 1500 and interval_time > 500:
                        interval_time = 2300
                    else:
                        interval_time = 2040
                    next_time = op_time + datetime.timedelta(seconds=interval_time)
                    next_time = next_time.strftime("%Y-%m-%d %H:%M:%S")
                else:
                    interval_time = 5320
                    op_time = datetime.datetime.now()
                    next_time = op_time + datetime.timedelta(seconds=interval_time)
                    next_time = next_time.strftime("%Y-%m-%d %H:%M:%S")
            else:
                interval_time = 2900
                op_time = datetime.datetime.now()
                next_time = op_time + datetime.timedelta(seconds=interval_time)
                next_time = next_time.strftime("%Y-%m-%d %H:%M:%S")
            context['payload'] = {"next_time": next_time, "picture": picture}
            context['errmsg'] = ''
            context['status'] = 0

        except Exception as e:
            context['errmsg'] = e
            context['status'] = 1
    return response(context)


@api_view(http_method_names=["GET"])
def get_copy1_worn(request):  # 原版
    content = {}
    if request.method == "GET":
        m_no = request.GET["m_no"]
        d_code = request.GET["d_code"]
        try:
            wheel_no = []  # 砂輪序號
            start_time = []  # 刀具開始生產時間
            w_gran = []
            t_name1, w_gran_data, position = '', '', ''
            t_name_sql = "select t_name from tb_productionmessage WHERE m_no={} ORDER BY id DESC limit 1".format(m_no)
            t_name_data_ = my_select(t_name_sql)  # 獲取刀具名稱

            m_no_data_sql = "select t_name,op_time from tb_productionmessage WHERE m_no={}".format(
                m_no)  # 獲取該幾台記錄以備找出生產該刀具的開始時間
            m_no_data = my_select(m_no_data_sql)
            if len(t_name_data_) != 0 and len(m_no_data) != 0:
                t_name1 = t_name_data_[0][0]
                t_name_data = t_name_data_[0][0]

                t_name = [i[0] for i in m_no_data]
                t_name = t_name[::-1]
                op_time = [i[1] for i in m_no_data]
                for i in range(0, len(t_name)):
                    if t_name[i] != t_name_data:
                        start_time.append(op_time[len(t_name) - i - 1])
                        break
                if len(start_time) == 0:
                    if t_name[len(t_name) - 1] == t_name_data:
                        start_time.append(op_time[0])
                me_no_data_sql = "select me_no,me_num from tb_productionmessage WHERE op_time>='{}' AND t_name='{}' AND d_code='{}' AND m_no={}".format(
                    start_time[0], t_name_data, d_code, m_no)
                me_no_data_ = my_select(me_no_data_sql)
                if len(me_no_data_) != 0:
                    me_no_data = [i[0] for i in me_no_data_]  # 檢測序列
                    me_num = [i[1] for i in me_no_data_]  # 檢測值
                    me_num_data = []
                    g_size_sql = "select g_size from tb_designsize WHERE t_name='{}' AND d_code='{}'".format(
                        t_name_data, d_code)  # 獲取標準值
                    g_size = my_select(g_size_sql)[0][0]

                    for i in me_num:
                        me_num_data.append(g_size - i)  # 計算磨損值

                    me_num_data1 = []
                    me_no_data1 = []
                    for i in range(0, len(me_num_data)):
                        if abs(me_num_data[i]) < 0.01:
                            me_num_data1.append(me_num_data[i])
                            me_no_data1.append(me_no_data[i])
                    x = get_value(me_no_data1)  # 刀具支數,橫坐標
                    # me_num_data1 = map(abs, me_num_data1)
                    y = get_value(me_num_data1)  # 刀具磨損，縱坐標

                    wheel_1_sql = "select wheel_1 from tb_productionmessage WHERE op_time>='{}' AND t_name='{}' AND d_code='{}' AND m_no={} AND wheel_1!='{}' AND wheel_1 IS NOT NULL ".format(
                        start_time[0], t_name_data, d_code, m_no, '')
                    wheel_2_sql = "select wheel_2 from tb_productionmessage WHERE op_time>='{}' AND t_name='{}' AND d_code='{}' AND m_no={} AND wheel_2!='{}' AND wheel_2 IS NOT NULL".format(
                        start_time[0], t_name_data, d_code, m_no, '')
                    wheel_3_sql = "select wheel_3 from tb_productionmessage WHERE op_time>='{}' AND t_name='{}' AND d_code='{}' AND m_no={} AND wheel_3!='{}' AND wheel_3 IS NOT NULL".format(
                        start_time[0], t_name_data, d_code, m_no, '')
                    wheel_1 = [i[0] for i in my_select(wheel_1_sql)]
                    wheel_2 = [i[0] for i in my_select(wheel_2_sql)]
                    wheel_3 = [i[0] for i in my_select(wheel_3_sql)]
                    for i in wheel_1:
                        # print(i)
                        wheel_no.append(i[0])
                    for i in wheel_2:
                        wheel_no.append(i[0])
                    for i in wheel_3:
                        wheel_no.append(i[0])
                    wheel_no = list(set(wheel_no))
                    if len(wheel_no) != 0:
                        for i in wheel_no:
                            w_gran_sql = "select w_gran from tb_wheelmessage WHERE t_name='{}' AND w_no='{}'".format(
                                t_name_data, i)
                            w_gran.append(my_select(w_gran_sql)[0][0])  # 查找砂輪序號粒度
                            if w_gran:
                                local_index = w_gran.index(min(w_gran))  # 取砂輪粒度最小位置
                                w_gran_data = wheel_no[local_index]  # 取出砂輪序號
                                position = '直徑'
                            else:
                                w_gran_data = 'A'
                                position = '直徑'
                    else:
                        position = '直徑'
                        w_gran_data = "A"
                else:
                    value = get_worn_list()
                    y = value["x"]
                    x = value["y"]
                    position = value["position"]
            else:
                value = get_worn_list()
                y = value["x"]
                x = value["y"]
                position = value["position"]
            if len(x) == 0 or len(y) == 0:
                value = get_worn_list()
                y = value["x"]
                x = value["y"]
                position = value["position"]
            content["payload"] = {"x": x, "y": y, "m_no": m_no, "t_name": t_name1, "wheel_no": w_gran_data,
                                  "d_code": d_code, "position": position}
            content["errmsg"] = ''
            content["status"] = 0
        except Exception as e:
            content["errmsg"] = e
            content["status"] = 1
    return response(content)


@api_view(http_method_names=["GET", "POST", "PUT", "DELETE"])  # 幾台負載
def get_pressure(request):
    content = {"payload": {"date": [], "value": []}, "errmsg": '', "status": 0}
    if request.method == "GET":
        try:
            collect_time = []
            press = []
            start_time = request.GET.get("start", None)
            end_time = request.GET.get("end", None)
            if not start_time and not end_time:
                hydra_msg = TbHydrapress.objects.values("update_time", "p_value")
            else:
                from datetime import datetime
                start = datetime.strptime(start_time, "%Y-%m-%d")
                end_time = end_time + ' 23:59:59'
                end = datetime.strptime(end_time, "%Y-%m-%d %H:%M:%S")
                hydra_msg = TbHydrapress.objects.filter(field_collection_date__gte=start,
                                                        field_collection_date__lte=end).values("update_date",
                                                                                               "p_value")

            for i in hydra_msg:
                collect_time.append(i["update_time"].strftime('%m-%d'))
                press.append(i["p_value"])
            if len(press) > 7:
                press = press[-7:]
                collect_time = collect_time[-7:]
            content["payload"]["date"] = collect_time
            content["payload"]["value"] = press
            content['errmsg'] = ''
            content['status'] = 0

        except Exception as e:
            content['errmsg'] = e
            content['status'] = 1
    return response(content)


@api_view(http_method_names=["GET"])
def get_worn(request):  # 第二版
    content = {}
    if request.method == "GET":
        m_no = request.GET["m_no"]
        d_code = request.GET["d_code"]
        try:
            wheel_no = []  # 砂輪序號
            start_time = []  # 刀具開始生產時間
            w_gran = []
            t_name1, w_gran_data, position = '', '', ''
            t_name_sql = "select t_name from tb_productionmessage WHERE m_no={} ORDER BY id DESC limit 1".format(m_no)
            t_name_data_ = my_select(t_name_sql)  # 獲取刀具名稱

            m_no_data_sql = "select t_name,op_time from tb_productionmessage WHERE m_no={}".format(
                m_no)  # 獲取該幾台記錄以備找出生產該刀具的開始時間
            m_no_data = my_select(m_no_data_sql)
            if len(t_name_data_) != 0 and len(m_no_data) != 0:
                # print(111)
                t_name1 = t_name_data_[0][0]
                t_name_data = t_name_data_[0][0]
                t_name = [i[0] for i in m_no_data]
                t_name = t_name[::-1]
                op_time = [i[1] for i in m_no_data]
                for i in range(0, len(t_name)):
                    if t_name[i] != t_name_data:
                        start_time.append(op_time[len(t_name) - i - 1])
                        break
                if len(start_time) == 0:
                    if t_name[len(t_name) - 1] == t_name_data:
                        start_time.append(op_time[0])

                me_no_data_sql = "select me_no from tb_productionmessage WHERE op_time>='{}' AND t_name='{}' AND d_code='{}' AND m_no={}".format(
                    start_time[0], t_name_data, d_code, m_no)
                me_no_data_ = my_select(me_no_data_sql)
                # print(222)
                if len(me_no_data_) != 0:
                    me_no_data = [i[0] for i in me_no_data_]  # 檢測序列
                    x = get_value(me_no_data)  # 刀具支數,橫坐標
                    wheel_sql = "select wheel_1,wheel_2,wheel_3 from tb_productionmessage WHERE op_time>='{}' AND t_name='{}' AND d_code='{}' AND m_no={}".format(
                        start_time[0], t_name_data, d_code, m_no)
                    wheel_1 = [i[0] for i in my_select(wheel_sql)]
                    wheel_2 = [i[1] for i in my_select(wheel_sql)]
                    wheel_3 = [i[2] for i in my_select(wheel_sql)]
                    wheel_no_list = []
                    # print(3333)
                    for i in wheel_1:
                        if i != '' and i is not None:
                            wheel_no.append(i[0])
                            wheel_no_list.append(i)
                    for i in wheel_2:
                        if i != '' and i is not None:
                            wheel_no.append(i[0])
                            wheel_no_list.append(i)
                    for i in wheel_3:
                        if i != '' and i is not None:
                            wheel_no.append(i[0])
                            wheel_no_list.append(i)
                    wheel_no = list(set(wheel_no))  # 將砂輪去重
                    if '+' in wheel_no:
                        wheel_no.remove('+')  # 刪除因錄入不符合的砂輪序號
                    if len(wheel_no) != 0:
                        for i in wheel_no:
                            w_gran_sql = "select w_gran from tb_wheelmessage WHERE t_name='{}' AND w_no='{}'".format(
                                t_name_data, i)
                            if my_select(w_gran_sql):
                                w_gran.append(my_select(w_gran_sql)[0][0])  # 查找砂輪序號粒度
                        if w_gran:
                            local_index = w_gran.index(min(w_gran))  # 取砂輪粒度最小位置
                            w_gran_data = wheel_no[local_index]  # 取出砂輪序號
                            position = '直徑'
                        else:
                            w_gran_data = 'A'
                            position = '直徑'
                    else:
                        position = '直徑'
                        w_gran_data = "A"
                    worn_list = []
                    # try:
                    for i in wheel_no_list:
                        if i[0] == w_gran_data:
                            wheel_ = i.split('+')
                            for j in wheel_:
                                worn_list.append(j)
                    worn = []
                    for i in worn_list:
                        if i[1] == '1' and len(i) > 5:
                            try:
                                if abs(float(i[3:])):
                                    worn.append(abs(float(i[3:])))
                            except:
                                pass
                    y = get_value(worn)
                    x_list = []
                    if len(y) != 0 and len(x) != y and len(x) != 0:  # 匹配x和y
                        a = len(x) / len(y)
                        a = int(a)
                        for i in range(0, len(x)):
                            b = i % a
                            if b == 0:
                                x_list.append(i)
                    x_temp = []
                    for i in x_list:
                        x_temp.append(x[i])
                    x = x_temp
                    x.insert(0, 0)
                    y.insert(0, 0)
                    if len(y) == 1:  # 如果沒有補償數據，生成模擬數據
                        # print(777)
                        value = get_worn_list()
                        y = value["x"]
                        x = value["y"]
                        position = value["position"]
                        # except:
                        #     value = get_worn_list()
                        #     y = value["x"]
                        #     x = value["y"]
                        #     position = value["position"]


                else:
                    value = get_worn_list()
                    y = value["x"]
                    x = value["y"]
                    position = value["position"]
            else:
                value = get_worn_list()
                y = value["x"]
                x = value["y"]
                position = value["position"]
            # print(worn)
            if len(y) < len(x):
                x = x[0:len(y)]
            content["payload"] = {"x": x, "y": y, "m_no": m_no, "t_name": t_name1, "wheel_no": w_gran_data,
                                  "d_code": d_code, "position": position}
            content["errmsg"] = ''
            content["status"] = 0
        except Exception as e:
            content["errmsg"] = e
            content["status"] = 1
    return response(content)


@api_view(http_method_names=["GET", "POST", "PUT", "DELETE"])  # 幾台負載
# def get_load_shock(request):
def loading_all(request):
    content = {}
    content['payload'] = {"loading": [], "current_time": [], "shock": []}
    content["errmsg"] = ''
    content["status"] = 0
    if request.method == "GET":
        try:
            shock, load, current = [], [], []
            M_NO = request.GET["m_no"]
            react = request.GET.get("react", 0)

            gw_id_sql = "select gw_id from view_total WHERE new_name={}".format(M_NO)
            gw_id = my_select(gw_id_sql)[0][0]
            cass_conn = cassandra_connect()
            if M_NO[1:7] == "SZ-620" or M_NO[1:7] == "SZ-628" or M_NO[1:8] == "SZ-NANO":  # 620和628入口
                status_cql = "select rc,time_current,run from st_romati_01 WHERE gw_id='{}' limit 1".format(gw_id)
                data_cql = cass_conn.execute(status_cql)
                run, rc, time_current = '', '', ''
                if not data_cql: raise Exception("該幾台還沒有採集過負載")
                if data_cql:
                    for i in data_cql:
                        run, rc, time_current = i.run, i.rc, i.time_current
                    try:
                        if not time_current or not run or not rc: raise Exception("數據丟失")
                        time_aa = time_current.split(':')
                        hour, minute, sencond = int(time_aa[0]), int(time_aa[1]), int(time_aa[2])
                        if run == 3:
                            start_time = rc - datetime.timedelta(hours=hour, minutes=minute, seconds=sencond)  # 刀具開始時間
                            tt = datetime.datetime.now()  # 當前時間
                            interval_ = tt - datetime.timedelta(hours=1)
                            if interval_ > start_time: raise Exception("機器已一個小時沒有數據上傳")

                            start_time = str(start_time) + '+0000'
                            load_sql = "select loading,time_current from st_romati_01 WHERE run=3 AND rc>='{}' AND gw_id='{}' limit 10 ALLOW FILTERING".format(
                                start_time, gw_id)

                            shock_cql = "select x_value from st_anca_02 WHERE gw_id='{}' AND mt>'{}' ALLOW FILTERING".format(
                                gw_id, start_time)
                            load_data = cass_conn.execute(load_sql)
                            shock_data = cass_conn.execute(shock_cql)

                            for i in load_data:
                                load.append(i.loading)
                                current.append(i.time_current)
                            current = sorted(current)
                            current = current[::-1]
                            if shock_data:
                                for i in shock_data:
                                    shock.append(i.x_value)
                            else:
                                lenght = len(load)

                                shock = list(round(random.uniform(0.66, 1.1), 2) for i in range(0, lenght))  # 生成隨機數
                    except Exception as e:
                        content["status"] = 1
                        content["errmsg"] = str(e)


            elif M_NO[1:7] == "SZ-TG2":
                status_cql = "select rc,current_time,isrunning from st_tgwx_01 WHERE gw_id='{}' limit 1".format(gw_id)
                data_cql = cass_conn.execute(status_cql)
                if not data_cql: raise Exception("該幾台還沒有採集過負載")
                for i in data_cql:
                    rc, current_time, isrunning = i.rc, i.current_time, i.isrunning
                if isrunning != 1: raise Exception("刀具未處於加工狀態")
                if not rc or not current_time or not isrunning: raise Exception("數據丟失")
                if rc + datetime.timedelta(hours=1) < datetime.datetime.now(): raise Exception("該幾台已經一個小時以上沒有數據了")
                if isrunning == 1:
                    hour, minute, sencond = int(current_time / 3600), int((current_time % 3600) / 60), int(
                        current_time % 60)
                    start_time = rc - datetime.timedelta(hours=hour, minutes=minute, seconds=sencond)
                    start_time = str(start_time) + '+0000'
                    load_and_time_cql = "select current_time,axis_c_load from st_tgwx_01 where gw_id='{}' and rc>'{}' AND isrunning=1 ORDER BY rc DESC limit 10 ALLOW FILTERING".format(
                        gw_id, start_time)
                    load_and_time = cass_conn.execute(load_and_time_cql)

                    shock_cql = "select x_value from st_anca_02 WHERE gw_id='{}' AND mt>'{}' ALLOW FILTERING".format(
                        gw_id, start_time)  # 查出震動
                    shock_data = cass_conn.execute(shock_cql)
                    for i in load_and_time:
                        load.append(i.axis_c_load)
                        current.append(i.current_time)
                    for index, value in enumerate(current):
                        hour_, minute_, sencond_ = int(value / 3600), int((value % 3600) / 60), int(value % 60)
                        # print(hour_,minute_,sencond_)
                        if hour_ < 10:
                            hour_ = '0' + str(hour_)
                        if minute_ < 10:
                            minute_ = '0' + str(minute_)
                        if sencond_ < 10:
                            sencond_ = '0' + str(sencond_)
                        current[index] = str(hour_) + ':' + str(minute_) + ':' + str(sencond_)

                    for index, value in enumerate(load):
                        load[index] = int(value)

                    if shock_data:
                        for i in shock_data:
                            shock.append(i.x_value)
                    else:
                        lenght = len(load)
                        shock = list(round(random.uniform(0.66, 1.1), 2) for i in range(0, lenght))  # 生成隨機數
                        # print("完成")

            elif M_NO[1:6] == "SZ-DK":
                # status_cql = "select rc,loop_time,light,part_counter from st_deckel_01 WHERE gw_id='{}' limit 1".format(gw_id)
                # data_cql = cass_conn.execute(status_cql)
                # light, rc, loop_time,part_counter = '', '', '',''
                # if not data_cql: raise Exception("該幾台還沒有採集過負載")
                # for i in data_cql:
                #     light,rc,loop_time,part_counter=i.light,i.rc,i.loop_time,i.part_counter
                # # print(type(loop_time),loop_time,len(loop_time))
                # # if not loop_time:
                # #     print(5555)
                # try:
                #     if light != "green": raise Exception("幾台未正常加工刀具,")
                #     if not light or not loop_time or not rc or not part_counter: raise Exception("數據丟失")
                #     # print(loop_time)
                #     current_time = loop_time.split('|')[0]
                #     if len(current_time)==6:
                #         current_time='00'+current_time
                #     time_aa = current_time.split(":")
                #     hour,minute,sencond = int(time_aa[0]),int(time_aa[1]),int(time_aa[2])
                #
                #     if light=="green":
                #         # print(3333,hour,minute,sencond)
                #         start_time = rc-datetime.timedelta(hours=hour,minutes=minute,seconds=sencond)
                #         # print(start_time)
                #         now = datetime.datetime.now()
                #         out_time = rc+datetime.timedelta(hours=1)
                #         # print(44)
                #         if out_time<now:raise Exception("該幾台已超過一個小以上沒有數據了")
                #
                #         start_time = str(start_time) + '+0000'
                #         load_sql = "select loading,loop_time from st_deckel_01 WHERE light=green AND rc>='{}' AND gw_id='{}' and part_counter={} ORDER BY rc DESC limit 10 ALLOW FILTERING".format(
                #             start_time, gw_id,part_counter)#查出負載
                #
                #         shock_cql = "select x_value from st_anca_02 WHERE gw_id='{}' AND mt>'{}' ALLOW FILTERING".format(gw_id, start_time)#查出震動
                #         shock_data = cass_conn.execute(shock_cql)
                #
                #         load_data = cass_conn.execute(load_sql)
                #         loading,loop_time_ = [],[]
                #         if load_data:
                #             for i in load_data:
                #                 loading.append(i.loading)
                #                 loop_time_.append(i.loop_time)
                #         for index,value in enumerate(loop_time):
                #             loop_time[index] = value.split('|')[0]
                #         for i in loop_time:
                #             current = i[1:]
                #
                #         if shock_data:
                #             for i in shock_data:
                #                 shock.append(i.x_value)
                #         else:
                #             lenght = len(load)
                #             shock = list(round(random.uniform(0.66, 1.1), 2) for i in range(0, lenght))  # 生成隨機數
                # except Exception as e:
                #     content["status"] = 1
                #     content["errmsg"] = str(e)
                pass

            else:
                status_cql = "select rc,current_time, status_light from st_anca_03 where gw_id='{}' limit 1".format(
                    gw_id)
                data_cql = cass_conn.execute(status_cql)
                status, rc, current_time = '', '', ''
                if not data_cql: raise Exception("幾台沒有採集過負載")
                if data_cql:
                    for i in data_cql:
                        status, rc, current_time = i.status_light, i.rc, i.current_time
                    try:
                        time_aa = current_time.split(':')
                        hour, minute, sencond = int(time_aa[0]), int(time_aa[1]), int(time_aa[2])
                        if status == "red" or status == "green-0": raise Exception("機器處於報警或者空轉狀態")
                        if status == "green" or status == "yellow":
                            start_time = rc - datetime.timedelta(hours=hour, minutes=minute, seconds=sencond)
                            tt = datetime.datetime.now()  # 生成當前時間，判斷是否停機，如果是，選擇模擬數據
                            tt1 = tt - datetime.timedelta(hours=1)
                            if tt1 > start_time: raise Exception("幾台已有一個小時沒有採集數據")

                            if tt1 < start_time:  # 如果時間過去太久，選擇模擬數據
                                # print(111)
                                start_time = str(start_time) + '+0000'
                                loading_cql = "select loading,current_time from st_anca_03 WHERE gw_id='{}' AND rc>='{}' AND status_light='{}' limit 10 ALLOW FILTERING".format(
                                    gw_id, start_time, status)
                                shock_cql = "select x_value from st_anca_02 WHERE gw_id='{}' AND mt>'{}' ALLOW FILTERING".format(
                                    gw_id, start_time)
                                loading_data = cass_conn.execute(loading_cql)
                                shock_data = cass_conn.execute(shock_cql)
                                for i in loading_data:
                                    load.append(i.loading)
                                    current.append(i.current_time)
                                current = sorted(current)
                                current = current[::-1]
                                if shock_data:
                                    for i in shock_data:
                                        shock.append(i.x_value)
                                else:
                                    lenght = len(load)

                                    shock = list(round(random.uniform(0.66, 1.1), 2) for i in range(0, lenght))  # 生成隨機數

                                if len(load) == 0:
                                    gw_id = 'gw_id_753092a4d10411e79d68005056b6166b'  # RX7-009
                                    # data = get_simulation_data(gw_id,cass_conn,current_time,mt,status,current)
                                    # current = data["current"]
                                    # load = data["load"]
                                    # shock = data["shock"]


                            else:  # 分支為模擬數據
                                gw_id = 'gw_id_753092a4d10411e79d68005056b6166b'  # RX7-009
                                # data = get_simulation_data(gw_id, cass_conn, current_time, mt, status, current)
                                # current = data["current"]
                                # load = data["load"]
                                # shock = data["shock"]


                        else:  # 模擬數據
                            tt = datetime.datetime.now()
                            interval_time = tt - datetime.timedelta(minutes=10)
                            if interval_time > rc:
                                gw_id = 'gw_id_56ff3164d10411e7a35f005056b6166b'  # RX7-007
                                # data = get_simulation_data(gw_id, cass_conn, current_time, mt, status, current)
                                # current = data["current"]
                                # load = data["load"]
                                # shock = data["shock"]

                    except Exception as e:
                        content["errmsg"] = str(e)
                        content["status"] = 0
                else:  # 模擬數據
                    gw_id = 'gw_id_5ef947b20d6111e8ae94005056b6166b'  # MX7-001
                    # RX7_007_status_cql = "select mt,current_time,status_light from st_anca_01 where gw_id='{}' limit 1".format(
                    #     gw_id)
                    # data_cql_007 = cass_conn.execute(RX7_007_status_cql)
                    # for i in data_cql_007:
                    #     mt, current_time,status = i.mt,i.current_time,i.status_light
                    # try:
                    #     time_aa = current_time.split(':')
                    #     hour, minute, sencond = int(time_aa[0]), int(time_aa[1]), int(time_aa[2])
                    #     if status=="green" or status=="yellow":
                    #         start_time = mt - datetime.timedelta(hours=hour, minutes=minute, seconds=sencond)
                    #         start_time = str(start_time) + '+0000'
                    #         loading_cql = "select current_time from st_anca_01 WHERE gw_id='{}' AND mt>='{}' AND status_light='{}' limit 10 ALLOW FILTERING".format(
                    #             gw_id, start_time, status)
                    #         current_time_data = cass_conn.execute(loading_cql)
                    #         for i in current_time_data:
                    #             current.append(i.current_time)
                    #
                    #         load = []
                    #         list1 = [0, 1,1,1,1, 3, 4, 6, 3]
                    #         for i in range(0, 10):
                    #             load.append(choice(list1))
                    #         shock = list(round(random.uniform(0.66, 1.1), 2) for i in range(0, 10))
                    #
                    #     else:
                    #         gw_id = 'gw_id_63585f6cd10411e7adc1005056b6166b'  # RX7-008
                    #         # RX7_007_status_cql = "select mt,current_time,status_light from st_anca_01 where gw_id='{}' limit 1".format(
                    #         #     gw_id)
                    #         # data_cql_007 = cass_conn.execute(RX7_007_status_cql)
                    #         # for i in data_cql_007:
                    #         #     mt, current_time, status = i.mt, i.current_time, i.status_light
                    #         # time_aa = current_time.split(':')
                    #         # hour, minute, sencond = int(time_aa[0]), int(time_aa[1]), int(time_aa[2])
                    #         # if status == "green" or status=="yellow":
                    #         #     start_time = mt - datetime.timedelta(hours=hour, minutes=minute, seconds=sencond)
                    #         #     start_time = str(start_time) + '+0000'
                    #         #     loading_cql = "select current_time from st_anca_01 WHERE gw_id='{}' AND mt>='{}' AND status_light='{}' limit 10 ALLOW FILTERING".format(
                    #         #         gw_id, start_time, status)
                    #         #     current_time_data = cass_conn.execute(loading_cql)
                    #         #     for i in current_time_data:
                    #         #         current.append(i.current_time)
                    #         #
                    #         #     load = []
                    #         #     list1 = [0, 1,1, 3,1, 4, 6, 3]
                    #         #     for i in range(0, 10):
                    #         #         load.append(choice(list1))
                    #         #     shock = list(round(random.uniform(0.66, 1.1), 2) for i in range(0, 10))
                    #         data = get_simulation_data(gw_id, cass_conn, current_time, mt, status, current)
                    #         current = data["current"]
                    #         load = data["load"]
                    #         shock = data["shock"]
                    # data = get_simulation_data(gw_id, cass_conn, current_time, mt, status, current)
                    # current = data["current"]
                    # load = data["load"]
                    # shock = data["shock"]

                    # except:
                    #     content["errmsg"]="數據未實時更新"
                    #     content["status"]=0

            if len(current) != len(load):
                lenght_time = len(current)
                load = load[0:lenght_time]
                shock = shock[0:lenght_time]
            load1 = load[::-1]
            current_time1 = current[::-1]
            shock1 = shock
            for index, value in enumerate(load1):  # 將空值的補1
                if value == '':
                    load1[index] = 1
            # for index, value in enumerate(load1):#將628或620機型的百分號去掉
            #     if value[-1]=='%':
            #         load1[index]=int(value[0:-1])
            for index, value in enumerate(load1):
                if int(value) > 30:
                    load1[index] = 1
            if len(load1) != 0:
                if react == '0':
                    pass
                else:
                    load1 = load[-1:]
                    current_time1 = current_time1[-1:]
                    shock1 = shock[-1:]

                content['payload'] = {"loading": load1, "current_time": current_time1, "shock": shock1}
                content['errmsg'] = ''
                content['status'] = 0
                cass_conn.shutdown()

        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 1
    return response(content)


@api_view(http_method_names=["GET", "POST", "PUT", "DELETE"])  # 幾台負載
# def get_load_shock(request):
# def loading_all(request):
def loading_and_shock(request):
    content = {}
    content['payload'] = {"loading": [], "current_time": [], "shock": []}
    content["errmsg"] = ''
    content["status"] = 0
    if request.method == "GET":
        try:
            shock, load, current = [], [], []
            M_NO = request.GET["m_no"]
            react = request.GET.get("react", 0)

            gw_id_sql = "select gw_id from view_total WHERE new_name={}".format(M_NO)
            gw_id = my_select(gw_id_sql)[0][0]
            cass_conn = cassandra_connect()
            if M_NO[1:7] == "SZ-620" or M_NO[1:7] == "SZ-628" or M_NO[1:8] == "SZ-NANO":  # 620和628入口
                status_cql = "select rc,time_current,run from st_romati_01 WHERE gw_id='{}' limit 1".format(gw_id)
                data_cql = cass_conn.execute(status_cql)
                run, rc, time_current = '', '', ''
                # if not data_cql: raise Exception("該幾台還沒有採集過負載")
                if data_cql:
                    for i in data_cql:
                        run, rc, time_current = i.run, i.rc, i.time_current
                    try:
                        # if not time_current or not run or not rc:raise Exception("數據丟失")
                        if time_current and run and rc:
                            time_aa = time_current.split(':')
                            hour, minute, sencond = int(time_aa[0]), int(time_aa[1]), int(time_aa[2])
                            if run == 3:
                                start_time = rc - datetime.timedelta(hours=hour, minutes=minute,
                                                                     seconds=sencond)  # 刀具開始時間
                                tt = datetime.datetime.now()  # 當前時間
                                interval_ = tt - datetime.timedelta(hours=1)
                                # if interval_>start_time:raise Exception("機器已一個小時沒有數據上傳")
                                if interval_ < start_time:

                                    start_time = str(start_time) + '+0000'
                                    load_sql = "select loading,time_current from st_romati_01 WHERE run=3 AND rc>='{}' AND gw_id='{}' limit 10 ALLOW FILTERING".format(
                                        start_time, gw_id)

                                    shock_cql = "select x_value from st_anca_02 WHERE gw_id='{}' AND mt>'{}' ALLOW FILTERING".format(
                                        gw_id, start_time)
                                    load_data = cass_conn.execute(load_sql)
                                    shock_data = cass_conn.execute(shock_cql)
                                    for i in load_data:
                                        load.append(i.loading)
                                        current.append(i.time_current)
                                    current = sorted(current)
                                    current = current[::-1]
                                    if shock_data:
                                        for i in shock_data:
                                            shock.append(i.x_value)
                                    else:
                                        lenght = len(load)

                                        shock = list(
                                            round(random.uniform(0.66, 1.1), 2) for i in range(0, lenght))  # 生成隨機數
                    except Exception as e:
                        content["status"] = 1
                        content["errmsg"] = str(e)
                if not load:
                    # load = [0,0,0,0,0,0,0,0,0,0]
                    # shock = list(round(random.uniform(0.66, 1.1), 2) for i in range(0, 10))
                    # current = ["00:00:13","00:00:17","00:00:23","00:00:28","00:00:33","00:00:38","00:00:43","00:00:48","00:00:53","00:00:58"]
                    # current = current[::-1]
                    gw_id = 'gw_id_5f38c7520d6111e8b102005056b6166b'
                    status_cql = "select rc,time_current,run from st_romati_01 WHERE gw_id='{}' limit 1".format(gw_id)
                    data_cql = cass_conn.execute(status_cql)
                    run, rc, time_current = '', '', ''
                    # if not data_cql: raise Exception("該幾台還沒有採集過負載")
                    if data_cql:
                        for i in data_cql:
                            run, rc, time_current = i.run, i.rc, i.time_current
                        try:
                            # if not time_current or not run or not rc:raise Exception("數據丟失")
                            if time_current and run and rc:
                                time_aa = time_current.split(':')
                                hour, minute, sencond = int(time_aa[0]), int(time_aa[1]), int(time_aa[2])
                                if run == 3:
                                    start_time = rc - datetime.timedelta(hours=hour, minutes=minute,
                                                                         seconds=sencond)  # 刀具開始時間
                                    tt = datetime.datetime.now()  # 當前時間
                                    interval_ = tt - datetime.timedelta(hours=1)
                                    # if interval_>start_time:raise Exception("機器已一個小時沒有數據上傳")
                                    if interval_ < start_time:

                                        start_time = str(start_time) + '+0000'
                                        load_sql = "select loading,time_current from st_romati_01 WHERE run=3 AND rc>='{}' AND gw_id='{}' limit 10 ALLOW FILTERING".format(
                                            start_time, gw_id)

                                        shock_cql = "select x_value from st_anca_02 WHERE gw_id='{}' AND mt>'{}' ALLOW FILTERING".format(
                                            gw_id, start_time)
                                        load_data = cass_conn.execute(load_sql)
                                        shock_data = cass_conn.execute(shock_cql)
                                        for i in load_data:
                                            load.append(i.loading)
                                            current.append(i.time_current)
                                        current = sorted(current)
                                        current = current[::-1]
                                        if shock_data:
                                            for i in shock_data:
                                                shock.append(i.x_value)
                                        else:
                                            lenght = len(load)
                                            shock = list(
                                                round(random.uniform(0.66, 1.1), 2) for i in range(0, lenght))  # 生成隨機數
                        except Exception as e:
                            content["status"] = 1
                            content["errmsg"] = str(e)


            elif M_NO[1:7] == "SZ-TG2":
                shock, load, current = [], [], []
                status_cql = "select rc,current_time,isrunning from st_tgwx_01 WHERE gw_id='{}' limit 1".format(gw_id)
                data_cql = cass_conn.execute(status_cql)
                # if not data_cql: raise Exception("該幾台還沒有採集過負載")
                if data_cql:
                    for i in data_cql:
                        rc, current_time, isrunning = i.rc, i.current_time, i.isrunning
                    # if isrunning !=1:raise Exception("刀具未處於加工狀態")
                    # if not rc or not current_time or not isrunning:raise Exception("數據丟失")
                    if isrunning == 1:
                        if rc and current_time and isrunning:
                            # if rc + datetime.timedelta(hours=1)<datetime.datetime.now():raise Exception("該幾台已經一個小時以上沒有數據了")
                            if rc + datetime.timedelta(hours=1) > datetime.datetime.now():
                                hour, minute, sencond = int(current_time / 3600), int((current_time % 3600) / 60), int(
                                    current_time % 60)
                                start_time = rc - datetime.timedelta(hours=hour, minutes=minute, seconds=sencond)
                                start_time = str(start_time) + '+0000'
                                load_and_time_cql = "select current_time,axis_c_load from st_tgwx_01 where gw_id='{}' and rc>'{}' AND isrunning=1 ORDER BY rc DESC limit 10 ALLOW FILTERING".format(
                                    gw_id, start_time)
                                load_and_time = cass_conn.execute(load_and_time_cql)

                                shock_cql = "select x_value from st_anca_02 WHERE gw_id='{}' AND mt>'{}' ALLOW FILTERING".format(
                                    gw_id, start_time)  # 查出震動
                                shock_data = cass_conn.execute(shock_cql)
                                for i in load_and_time:
                                    load.append(i.axis_c_load)
                                    current.append(i.current_time)
                                for index, value in enumerate(current):
                                    hour_, minute_, sencond_ = int(value / 3600), int((value % 3600) / 60), int(
                                        value % 60)
                                    # print(hour_,minute_,sencond_)
                                    if hour_ < 10:
                                        hour_ = '0' + str(hour_)
                                    if minute_ < 10:
                                        minute_ = '0' + str(minute_)
                                    if sencond_ < 10:
                                        sencond_ = '0' + str(sencond_)
                                    current[index] = str(hour_) + ':' + str(minute_) + ':' + str(sencond_)

                                for index, value in enumerate(load):
                                    load[index] = int(value)

                                if shock_data:
                                    for i in shock_data:
                                        shock.append(i.x_value)
                                else:
                                    lenght = len(load)
                                    shock = list(round(random.uniform(0.66, 1.1), 2) for i in range(0, lenght))  # 生成隨機數

                                    # print("完成")
                if not load:
                    load = [0, 0, 0, 0, 0, 0, 0, 0, 0, 0]
                    shock = list(round(random.uniform(0.66, 1.1), 2) for i in range(0, 10))
                    current = ["00:03:15", "00:03:20", "00:03:25", "00:03:30", "00:03:35", "00:03:40", "00:03:45",
                               "00:03:50", "00:03:55", "00:04:00"]
                    current = current[::-1]


            elif M_NO[1:6] == "SZ-DK":
                # status_cql = "select rc,loop_time,light,part_counter from st_deckel_01 WHERE gw_id='{}' limit 1".format(gw_id)
                # data_cql = cass_conn.execute(status_cql)
                # light, rc, loop_time,part_counter = '', '', '',''
                # if not data_cql: raise Exception("該幾台還沒有採集過負載")
                # for i in data_cql:
                #     light,rc,loop_time,part_counter=i.light,i.rc,i.loop_time,i.part_counter
                # # print(type(loop_time),loop_time,len(loop_time))
                # # if not loop_time:
                # #     print(5555)
                # try:
                #     if light != "green": raise Exception("幾台未正常加工刀具,")
                #     if not light or not loop_time or not rc or not part_counter: raise Exception("數據丟失")
                #     # print(loop_time)
                #     current_time = loop_time.split('|')[0]
                #     if len(current_time)==6:
                #         current_time='00'+current_time
                #     time_aa = current_time.split(":")
                #     hour,minute,sencond = int(time_aa[0]),int(time_aa[1]),int(time_aa[2])
                #
                #     if light=="green":
                #         # print(3333,hour,minute,sencond)
                #         start_time = rc-datetime.timedelta(hours=hour,minutes=minute,seconds=sencond)
                #         # print(start_time)
                #         now = datetime.datetime.now()
                #         out_time = rc+datetime.timedelta(hours=1)
                #         # print(44)
                #         if out_time<now:raise Exception("該幾台已超過一個小以上沒有數據了")
                #
                #         start_time = str(start_time) + '+0000'
                #         load_sql = "select loading,loop_time from st_deckel_01 WHERE light=green AND rc>='{}' AND gw_id='{}' and part_counter={} ORDER BY rc DESC limit 10 ALLOW FILTERING".format(
                #             start_time, gw_id,part_counter)#查出負載
                #
                #         shock_cql = "select x_value from st_anca_02 WHERE gw_id='{}' AND mt>'{}' ALLOW FILTERING".format(gw_id, start_time)#查出震動
                #         shock_data = cass_conn.execute(shock_cql)
                #
                #         load_data = cass_conn.execute(load_sql)
                #         loading,loop_time_ = [],[]
                #         if load_data:
                #             for i in load_data:
                #                 loading.append(i.loading)
                #                 loop_time_.append(i.loop_time)
                #         for index,value in enumerate(loop_time):
                #             loop_time[index] = value.split('|')[0]
                #         for i in loop_time:
                #             current = i[1:]
                #
                #         if shock_data:
                #             for i in shock_data:
                #                 shock.append(i.x_value)
                #         else:
                #             lenght = len(load)
                #             shock = list(round(random.uniform(0.66, 1.1), 2) for i in range(0, lenght))  # 生成隨機數
                # except Exception as e:
                #     content["status"] = 1
                #     content["errmsg"] = str(e)

                # load = [0, 0, 0, 0, 0, 0, 0, 0, 0, 0]
                # shock = list(round(random.uniform(0.66, 1.1), 2) for i in range(0, 10))
                # current = ["00:01:23", "00:01:28", "00:01:33", "00:01:38", "00:01:43", "00:01:48", "00:01:53",
                #            "00:01:58", "00:02:03", "00:02:08"]
                # current = current[::-1]
                gw_id = 'gw_id_5ebf5a160d6111e8ab2b005056b6166b'
                status_cql = "select rc,time_current,run from st_romati_01 WHERE gw_id='{}' limit 1".format(gw_id)
                data_cql = cass_conn.execute(status_cql)
                run, rc, time_current = '', '', ''
                # if not data_cql: raise Exception("該幾台還沒有採集過負載")
                if data_cql:
                    for i in data_cql:
                        run, rc, time_current = i.run, i.rc, i.time_current
                    try:
                        # if not time_current or not run or not rc:raise Exception("數據丟失")
                        if time_current and run and rc:
                            time_aa = time_current.split(':')
                            hour, minute, sencond = int(time_aa[0]), int(time_aa[1]), int(time_aa[2])
                            if run == 3:
                                start_time = rc - datetime.timedelta(hours=hour, minutes=minute,
                                                                     seconds=sencond)  # 刀具開始時間
                                tt = datetime.datetime.now()  # 當前時間
                                interval_ = tt - datetime.timedelta(hours=1)
                                # if interval_>start_time:raise Exception("機器已一個小時沒有數據上傳")
                                if interval_ < start_time:

                                    start_time = str(start_time) + '+0000'
                                    load_sql = "select loading,time_current from st_romati_01 WHERE run=3 AND rc>='{}' AND gw_id='{}' limit 10 ALLOW FILTERING".format(
                                        start_time, gw_id)

                                    shock_cql = "select x_value from st_anca_02 WHERE gw_id='{}' AND mt>'{}' ALLOW FILTERING".format(
                                        gw_id, start_time)
                                    load_data = cass_conn.execute(load_sql)
                                    shock_data = cass_conn.execute(shock_cql)
                                    for i in load_data:
                                        load.append(i.loading)
                                        current.append(i.time_current)
                                    current = sorted(current)
                                    current = current[::-1]
                                    if shock_data:
                                        for i in shock_data:
                                            shock.append(i.x_value)
                                    else:
                                        lenght = len(load)
                                        shock = list(
                                            round(random.uniform(0.66, 1.1), 2) for i in range(0, lenght))  # 生成隨機數
                    except Exception as e:
                        content["status"] = 1
                        content["errmsg"] = str(e)

            else:
                status_cql = "select mt,current_time, status_light from st_anca_03 where gw_id='{}' limit 1".format(
                    gw_id)
                data_cql = cass_conn.execute(status_cql)
                status, mt, current_time = '', '', ''
                # if not data_cql:raise Exception("幾台沒有採集過負載")
                if data_cql:
                    for i in data_cql:
                        status, mt, current_time = i.status_light, i.mt, i.current_time
                    try:
                        time_aa = current_time.split(':')
                        hour, minute, sencond = int(time_aa[0]), int(time_aa[1]), int(time_aa[2])
                        # if status =="red" or status=="green-0":raise Exception("機器處於報警或者空轉狀態")
                        if status == "green" or status == "yellow":
                            start_time = mt - datetime.timedelta(hours=hour, minutes=minute, seconds=sencond)
                            tt = datetime.datetime.now()  # 生成當前時間，判斷是否停機，如果是，選擇模擬數據
                            tt1 = tt - datetime.timedelta(hours=1)
                            # if tt1>start_time:raise Exception("幾台已有一個小時沒有採集數據")

                            if tt1 < start_time:  # 如果時間過去太久，選擇模擬數據
                                # print(111)
                                start_time = str(start_time) + '+0000'
                                loading_cql = "select loading,current_time from st_anca_03 WHERE gw_id='{}' AND mt>='{}' AND status_light='{}' limit 10 ALLOW FILTERING".format(
                                    gw_id, start_time, status)
                                shock_cql = "select x_value from st_anca_02 WHERE gw_id='{}' AND mt>'{}' ALLOW FILTERING".format(
                                    gw_id, start_time)
                                loading_data = cass_conn.execute(loading_cql)
                                shock_data = cass_conn.execute(shock_cql)
                                for i in loading_data:
                                    load.append(i.loading)
                                    current.append(i.current_time)
                                current = sorted(current)
                                current = current[::-1]
                                if shock_data:
                                    for i in shock_data:
                                        shock.append(i.x_value)
                                else:
                                    lenght = len(load)

                                    shock = list(round(random.uniform(0.66, 1.1), 2) for i in range(0, lenght))  # 生成隨機數

                                if len(load) == 0:
                                    gw_id = 'gw_id_753092a4d10411e79d68005056b6166b'  # RX7-009
                                    data = get_simulation_data(gw_id, cass_conn, current_time, mt, status, current)
                                    current = data["current"]
                                    load = data["load"]
                                    shock = data["shock"]


                            else:  # 分支為模擬數據
                                gw_id = 'gw_id_2e64b134d10411e7adc1005056b6166b'  # RX7-009
                                data = get_simulation_data(gw_id, cass_conn, current_time, mt, status, current)
                                current = data["current"]
                                load = data["load"]
                                shock = data["shock"]


                        else:  # 模擬數據
                            tt = datetime.datetime.now()
                            interval_time = tt - datetime.timedelta(minutes=10)
                            if interval_time > mt:
                                gw_id = 'gw_id_56ff3164d10411e7a35f005056b6166b'  # RX7-007
                                data = get_simulation_data(gw_id, cass_conn, current_time, mt, status, current)
                                current = data["current"]
                                load = data["load"]
                                shock = data["shock"]

                    except Exception as e:
                        content["errmsg"] = str(e)
                        content["status"] = 0
                else:  # 模擬數據
                    gw_id = 'gw_id_5ef947b20d6111e8ae94005056b6166b'  # MX7-001
                    RX7_007_status_cql = "select mt,current_time,status_light from st_anca_01 where gw_id='{}' limit 1".format(
                        gw_id)
                    data_cql_007 = cass_conn.execute(RX7_007_status_cql)
                    for i in data_cql_007:
                        mt, current_time, status = i.mt, i.current_time, i.status_light
                    try:
                        time_aa = current_time.split(':')
                        hour, minute, sencond = int(time_aa[0]), int(time_aa[1]), int(time_aa[2])
                        if status == "green" or status == "yellow":
                            start_time = mt - datetime.timedelta(hours=hour, minutes=minute, seconds=sencond)
                            start_time = str(start_time) + '+0000'
                            loading_cql = "select current_time from st_anca_01 WHERE gw_id='{}' AND mt>='{}' AND status_light='{}' limit 10 ALLOW FILTERING".format(
                                gw_id, start_time, status)
                            current_time_data = cass_conn.execute(loading_cql)
                            for i in current_time_data:
                                current.append(i.current_time)

                            load = []
                            list1 = [0, 1, 1, 1, 1, 3, 4, 6, 3]
                            for i in range(0, 10):
                                load.append(choice(list1))
                            shock = list(round(random.uniform(0.66, 1.1), 2) for i in range(0, 10))

                        else:
                            gw_id = 'gw_id_63585f6cd10411e7adc1005056b6166b'  # RX7-008
                            data = get_simulation_data(gw_id, cass_conn, current_time, mt, status, current)
                            current = data["current"]
                            load = data["load"]
                            shock = data["shock"]

                    except:
                        content["errmsg"] = "數據未實時更新"
                        content["status"] = 0

            if len(current) != len(load):
                lenght_time = len(current)
                load = load[0:lenght_time]
                shock = shock[0:lenght_time]
            load1 = load[::-1]
            current_time1 = current[::-1]
            shock1 = shock
            for index, value in enumerate(load1):  # 將空值的補1
                if value == '':
                    load1[index] = 1
                if value is int:
                    load1[index] = str(load[index])
            # for index, value in enumerate(load1):#將628或620機型的百分號去掉
            #     if value[-1]=='%':
            #         load1[index]=int(value[0:-1])
            for index, value in enumerate(load1):
                if int(value) > 30:
                    load1[index] = 1
            if len(load1) != 0:
                if react == '0':
                    pass
                else:
                    load1 = load[-1:]
                    current_time1 = current_time1[-1:]
                    shock1 = shock[-1:]

                content['payload'] = {"loading": load1, "current_time": current_time1, "shock": shock1}
                content['errmsg'] = ''
                content['status'] = 0
                cass_conn.shutdown()

        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 1
    return response(content)


@api_view(http_method_names=["GET", "PUT", "DELETE", "POST"])
def get_post_machine(request):
    context = {}
    if request.method == "GET":
        try:
            data, status_list, position = [], [], []
            site = request.GET.get("site", None)
            no = request.GET.get("sort", None)
            image_line_sections_sql = SQL["img_line_sections_name_addr"].format(site)
            image_line_sections_data = my_select(image_line_sections_sql)
            image_url = [i[0] for i in image_line_sections_data]
            line_list = [i[1] for i in image_line_sections_data]
            sections_list = [i[2] for i in image_line_sections_data]
            new_name_list = [i[3] for i in image_line_sections_data]
            gw_name_list = [i[4] for i in image_line_sections_data]
            ip_address_list = [i[5] for i in image_line_sections_data]
            line_list_data = list(set(line_list))  # 線別

            def getSorted(list):
                return sorted(list)

            sections_list_data = getSorted(list(set(sections_list)))  # 車間

            for i in gw_name_list:
                position.append(int(i[12:]))
            for i in new_name_list:
                count = 0
                t_name_sql = SQL["by_machine_get_t_name"].format(i)
                if len(my_select(t_name_sql)) == 0:  # 判斷該幾台是否加工過刀具
                    status_list.append(count)
                    continue
                else:
                    t_name = my_select(t_name_sql)[0][0]
                    op_time = my_select(t_name_sql)[0][1]
                t_code_sql = SQL["get_d_code"].format(
                    t_name, op_time)  # 取出每個尺寸代碼
                t_code = [i[0] for i in my_select(t_code_sql)]
                for j in t_code:
                    wheel_data = TbProductionmessage.objects.filter(d_code=j, t_name=t_name,
                                                                    op_time=op_time).all().reverse()
                    if wheel_data:
                        wheel_data = wheel_data[0]
                        if (
                                            wheel_data.wheel_1 is None and wheel_data.wheel_2 is None and wheel_data.wheel_3 is None) or (
                                            wheel_data.wheel_1 == '' and wheel_data.wheel_2 == '' and wheel_data.wheel_3 == ''):
                            pass
                        else:
                            count = 1
                status_list.append(count)
            for i in gw_name_list:
                position.append(int(i[12:]))
            machines1 = []
            for j in sections_list_data:
                dict = {
                    'sections': j,
                    'machines': []
                }
                machines = []
                for i in range(0, len(image_url)):
                    if sections_list[i] == j:
                        dict_data = {}
                        dict_data["name"] = new_name_list[i]
                        dict_data["status"] = status_list[i]
                        dict_data["image"] = image_url[i]
                        dict_data["line"] = line_list[i]
                        dict_data["sections"] = sections_list[i]
                        dict_data["position"] = position[i]
                        dict_data["ip_address"] = ip_address_list[i]
                        machines.append(dict_data)
                dict['length'] = len(machines)
                for i in machines:
                    machines1.append(i)

                def getLine(x):
                    return x['line']

                lines = getSorted(list(set(list(map(getLine, machines)))))
                for k in lines:
                    mach = {
                        'line': k,
                        'data': []
                    }
                    _data = []
                    for m in machines:
                        if m['line'] == k:
                            _data.append(m)

                    def position_sort(L, position):
                        length = len(L)
                        for i in range(len(L) - 1):
                            for j in range(len(L) - 1):
                                if (L[j][position] > L[j + 1][position]):
                                    tmp = L[j]
                                    L[j] = L[j + 1]
                                    L[j + 1] = tmp
                        return L

                    data1 = position_sort(_data, 'position')

                    mach['data'] = data1
                    dict['machines'].append(mach)

                data.append(dict)

            # f2 = sorted(data, key=lambda x: x[''])
            # print(len(machines1))
            if no == '0':
                context["payload"] = data
            else:
                context["payload"] = machines1
            context['status'] = 0
            context['errmsg'] = ''
        except Exception as e:
            context['errmsg'] = e
            context["status"] = 1

    if request.method == "POST":
        pass
    return response(context)


@api_view(http_method_names=["GET", "POST"])
def translate_file(request, pk):
    content = {"payload": "", "status": 0, "errmsg": ""}
    if request.method == "POST":
        m_no = request.data['m_no']  # 幾台編號
        data = json.loads(request.data['data'])
        me_num_list, g_size_list, d_code_list, g_otol_list, g_itol_list, scope_list = [], [], [], [], [], []
        try:
            t_msg = TbDesignmessage.objects.filter(id=pk).values("t_name", "t_ver")
            for i in data:
                if i["me_num"]:
                    me_num_list.append(float(i["me_num"]))
                    d_code_list.append(i["d_code"])

            for i in range(0, len(d_code_list)):
                g_size_msg = TbDesignsize.objects.filter(d_code=d_code_list[i], t_name=t_msg[0]["t_name"],
                                                         t_ver=t_msg[0]["t_ver"]).values(
                    "g_size", "g_otol", "g_itol")
                g_size_list.append(g_size_msg[0]["g_size"])
                g_itol_list.append(g_size_msg[0]["g_itol"])
                g_otol_list.append(g_size_msg[0]["g_otol"])

                if 'α' in d_code_list[i]:
                    d_code_list[i] = d_code_list[i].replace('α', 'alpha')
                elif 'β' in d_code_list[i]:
                    d_code_list[i] = d_code_list[i].replace("β", 'beta')
                elif d_code_list[i] == 'Δ':
                    d_code_list[i] = 'delta'
                elif 'θ' in d_code_list[i]:
                    d_code_list[i] = d_code_list[i].replace("θ", 'theta')
                elif 'λ' in d_code_list[i]:
                    d_code_list[i] = d_code_list[i].replace("λ", 'lambda')
                elif d_code_list[i] == 'τ':
                    d_code_list[i] = 'tau'
                else:
                    pass
            out_list = []  # 把超差的篩選出來
            if me_num_list:
                for i in range(0, len(me_num_list)):
                    if me_num_list[i] > (g_size_list[i] + g_otol_list[i]) or me_num_list[i] < (
                                g_size_list[i] + g_itol_list[i]):
                        out_list.append(i)
            # print(out_list)
            g_size_ = []
            for i in out_list:
                g_size_.append(round(((g_size_list[i] + g_itol_list[i]) + (g_size_list[i] + g_otol_list[i])) / 2, 3))
            # print("g_size_list",g_size_list)
            me_num, g_size, d_code = '', '', ''
            for i in out_list:
                me_num = me_num + str(me_num_list[i]) + ','
            for i in g_size_:
                g_size = g_size + str(i) + ','
            for i in out_list:
                d_code = d_code + d_code_list[i] + ','
            me_num, g_size, d_code = me_num[:-1], g_size[:-1], d_code[:-1]
            ip = my_select(SQL["get_ip"].format(m_no))
            # print(ip)
            if ip:
                ip_str = ip[0][0].split(".")
                if len(ip_str) != 4: raise Exception("該幾台的IP無效！")
                ip = ip[0][0]
                # ip = "10.148.88.177"
                file_handled = TranslateFile(ip, settings.FILE_TRANSLATE_PORT,
                                             settings.RECV_BUFFER, me_num, g_size, d_code)  # 注意！沒有用到鎖
                message = file_handled.adjust()
                content["payload"] = message
        except Exception as e:
            content["errmsg"] = str(e)
            content["status"] = 1

    return response(content)


@api_view(http_method_names=["GET"])
def get_m_type(request):
    content = {"payload": [], "status": 0, "errmsg": ""}
    try:
        m_type = TbMachine.objects.values("m_type").distinct()
        for i in m_type:
            content["payload"].append(i["m_type"])
    except Exception as e:
        content["status"] = 1
        content["errmsg"] = str(e)
    return Response(content)


@api_view(http_method_names=["GET"])
def get_t_type(request):
    content = {"payload": [], "status": 0, "errmsg": ""}
    try:
        c_type = TbCTypeHead.objects.values("c_type")
        for i in c_type:
            content["payload"].append(i["c_type"])
    except Exception as e:
        content["status"] = 1
        content['errmsg'] = str(e)
    return Response(content)


@api_view(http_method_names=["GET"])
def brand_percent(request):
    content = {"payload": {}, "status": 0, "errmsg": ""}
    if request.method == "GET":
        start = request.GET.get("start", None)
        end = request.GET.get("end", None)
        m_type = request.GET.get("type", None)
        try:
            if start and end and m_type:
                w_brans = TbWheelSort.objects.filter(m_no__icontains=m_type, update_time__gte=start,
                                                     update_time__lte=end).values("w_bran")
            elif start and end and m_type is None:
                w_brans = TbWheelSort.objects.filter(update_time__gte=start,
                                                     update_time__lte=end).values("w_bran")
            elif start is None and end is None and m_type:
                w_brans = TbWheelSort.objects.filter(m_no__icontains=m_type).values("w_bran")
            else:
                w_brans = TbWheelSort.objects.values("w_bran")
            sorts = TbBrand.objects.values("w_brand")
            for sort in sorts:
                content["payload"][sort["w_brand"]] = 0
                for w_bran in w_brans:
                    if sort["w_brand"] == w_bran["w_bran"]:
                        content["payload"][sort["w_brand"]] += 1

            for sort in sorts:
                content["payload"][sort["w_brand"]] = round((content["payload"][sort["w_brand"]]) / (len(w_brans)), 4)
            content["payload"] = dict(filter(lambda x: x[1] != 0, content["payload"].items()))  # 去掉dict中为0 的数据
        except Exception as e:
            content["status"] = 1
            content["errmsg"] = str(e)
        return response(content)


@api_view(http_method_names=["GET"])
def percent(request):
    content = {"payload": [], "status": 0, "errmsg": ""}
    if request.method == "GET":
        start = request.GET.get("start", None)
        end = request.GET.get("end", None)
        m_type = request.GET.get("type", None)
        try:
            data = {}
            if start and end and m_type:
                w_brans = TbWheelSort.objects.filter(m_no__icontains=m_type, update_time__gte=start,
                                                     update_time__lte=end).values("w_bran")
            elif start and end and m_type is None:
                w_brans = TbWheelSort.objects.filter(update_time__gte=start,
                                                     update_time__lte=end).values("w_bran")
            elif start is None and end is None and m_type:
                w_brans = TbWheelSort.objects.filter(m_no__icontains=m_type).values("w_bran")
            else:
                w_brans = TbWheelSort.objects.values("w_bran")
            sorts = TbBrand.objects.values("w_brand")
            for sort in sorts:
                data[sort["w_brand"]] = 0
                for w_bran in w_brans:
                    if sort["w_brand"] == w_bran["w_bran"]:
                        data[sort["w_brand"]] += 1

            for sort in sorts:
                data[sort["w_brand"]] = round((data[sort["w_brand"]]) / (len(w_brans)), 4)
            data = dict(filter(lambda x: x[1] != 0, data.items()))  # 去掉dict中为0 的数据
            content["payload"].append(data)
        except Exception as e:
            content["status"] = 1
            content["errmsg"] = str(e)
        return response(content)


@api_view(http_method_names=["GET"])
def data_percent(request):
    content = {"payload": [], "status": 0, "errmsg": ""}
    if request.method == "GET":
        start = request.GET.get("start", None)
        end = request.GET.get("end", None)
        m_type = request.GET.get("m_type", None)

        if start is None and end is None:
            t1, t2 = start_end_time()
            start, end = t1, t2
        else:
            if start > end: raise Exception(u'開始時間不能大於結束時間')
        try:
            if m_type:
                w_brans = TbWheelSort.objects.filter(m_no__icontains=m_type, update_time__gte=start,
                                                     update_time__lte=end).values("w_bran")
            else:
                w_brans = TbWheelSort.objects.filter(update_time__gte=start, update_time__lte=end).values("w_bran")
            sorts = TbBrand.objects.values("w_brand")
            tmp_list = []
            for sort in sorts:
                data = {}
                data["name"] = sort["w_brand"]
                data["value"] = 0
                for w_bran in w_brans:
                    if sort["w_brand"] == w_bran["w_bran"]:
                        data["value"] += 1
                tmp_list.append(data)
            total = len(w_brans)
            for i in tmp_list:
                i["value"] = round(i["value"] / total, 4)
            # print(content["payload"])
            for i in tmp_list:
                if i["value"] != 0:
                    content["payload"].append(i)
        except Exception as e:
            content["status"] = 1
            content["errmsg"] = str(e)
        return response(content)


@api_view(http_method_names=["GET"])
def tool_quantity(request):
    content = {"payload": {}, "status": 0, "errmsg": ""}
    if request.method == "GET":
        start = request.GET.get("start", None)
        end = request.GET.get("end", None)
        m_type = request.GET.get("m_type", None)

        if start is None and end is None:
            t1, t2 = start_end_time()
            start, end = t1, t2
        else:
            if start > end: raise Exception(u'開始時間不能大於結束時間')
        # print(m_type)
        try:
            if m_type:
                wheel_data = TbWheelSort.objects.filter(m_no__icontains=m_type, update_time__gte=start,
                                                        update_time__lte=end).values("t_name", "w_bran", "update_time",
                                                                                     "uuid").distinct()  # distinct()去重

            else:
                wheel_data = TbWheelSort.objects.filter(update_time__gte=start, update_time__lte=end).values("t_name",
                                                                                                             "w_bran",
                                                                                                             "update_time",
                                                                                                             "uuid").distinct()
            sorts = TbBrand.objects.values("w_brand")  # 品牌類型
            tool_types = TbCTypeHead.objects.values("c_type")  # 刀具類型
            content["payload"]["axisData"] = []
            for i in sorts:
                content["payload"]["axisData"].append(i["w_brand"])
            content["payload"]["series"] = []

            for tool in tool_types:
                tmp_data = {"name": '', "value": []}
                tmp_data["name"] = tool["c_type"]
                t_name = TbDesignmessage.objects.filter(t_type=tool["c_type"]).values(
                    "t_name").distinct()  # 查出該刀具類型所有的刀具名稱
                t_name = [i["t_name"] for i in t_name]
                for sort in content["payload"]["axisData"]:
                    sort_count = 0
                    # content["payload"][tool["c_type"]][sort["w_brand"]] = 0
                    for wheel in wheel_data:
                        if (wheel["t_name"] in t_name) and wheel["w_bran"] == sort:
                            sort_count += 1
                    tmp_data["value"].append(sort_count)
                content["payload"]["series"].append(tmp_data)

        except Exception as e:
            content["status"] = 1
            content["errmsg"] = str(e)
        return response(content)


@api_view(http_method_names=["GET"])
def brand_tool_quantity(request):
    content = {"payload": {}, "status": 0, "errmsg": ""}
    if request.method == "GET":
        start = request.GET.get("start", None)
        end = request.GET.get("end", None)
        m_type = request.GET.get("m_type", None)

        if start is None and end is None:
            t1, t2 = start_end_time()
            start, end = t1, t2
        else:
            if start > end: raise Exception(u'開始時間不能大於結束時間')
        try:
            if m_type:
                wheel_data = TbWheelSort.objects.filter(m_no__icontains=m_type, update_time__gte=start,
                                                        update_time__lte=end).values("t_name", "w_bran", "update_time",
                                                                                     "uuid").distinct()  # distinct()去重

            else:
                wheel_data = TbWheelSort.objects.filter(update_time__gte=start, update_time__lte=end).values("t_name",
                                                                                                             "w_bran",
                                                                                                             "update_time",
                                                                                                             "uuid").distinct()  # distinct()去重

            tool_types = TbCTypeHead.objects.values("c_type")  # 查出刀具類型
            brand_sorts = TbBrand.objects.values("w_brand")  # 品牌類型

            content["payload"]["axisData"] = []
            for i in brand_sorts:
                content["payload"]["axisData"].append(i["w_brand"])

            content["payload"]["series"] = []

            for tool in tool_types:  # 按刀具類型分類
                tmp_dict = {"name": '', "data": []}
                tmp_dict["name"] = tool["c_type"]
                t_name_des = TbDesignmessage.objects.filter(t_type=tool["c_type"]).values(
                    "t_name").distinct()  # 查出該刀具類型所有的刀具名稱
                t_name_des = [i["t_name"] for i in t_name_des]
                for sort in content["payload"]["axisData"]:  # 按刀具類型下的砂輪品牌分類
                    tmp_count = 0
                    uuid_list = []
                    for wheel in wheel_data:
                        if wheel["t_name"] in t_name_des and wheel["w_bran"] == sort:
                            uuid_list.append(wheel["uuid"])
                    uuid_list = list(set(uuid_list))
                    if uuid_list:
                        for i in uuid_list:
                            product_message = TbProductionmessage.objects.filter(uuid=i, op_time__gte=start,
                                                                                 op_time__lte=end).values("me_no",
                                                                                                          "t_no").distinct()
                            if product_message:
                                for me_no in product_message:
                                    if me_no["me_no"] is not None and me_no["me_no"] != '':
                                        if me_no["me_no"] > 1:
                                            tmp_count += (me_no["me_no"] + 1)
                                        else:
                                            tmp_count += me_no["me_no"]
                    tmp_dict["data"].append(tmp_count)
                content["payload"]["series"].append(tmp_dict)

        except Exception as e:
            content["status"] = 1
            content["errmsg"] = str(e)
        return response(content)


@api_view(http_method_names=["GET"])
def repair_frequency(request):
    content = {"payload": {"axisData": [], "series": []}, "errmsg": "", "status": 0}
    if request.method == "GET":
        start = request.GET.get("start", None)
        end = request.GET.get("end", None)
        m_type = request.GET.get("m_type", None)
        if start is None and end is None:
            t1, t2 = start_end_time()
            start, end = t1, t2
        else:
            if start > end: raise Exception(u'開始時間不能大於結束時間')
        try:
            if m_type:
                uuid_data = TbProductionmessage.objects.filter(m_no__contains=m_type, op_time__gte=start,
                                                               op_time__lte=end, status=5).exclude(
                    wheel_1__isnull=True, wheel_2__isnull=True,
                    wheel_3__isnull=True).exclude(wheel_1__exact='', wheel_2__exact='', wheel_3__exact='').values(
                    "uuid", "t_name").distinct()
            else:
                uuid_data = TbProductionmessage.objects.filter(op_time__gte=start, op_time__lte=end,
                                                               uuid__isnull=False, status=5).exclude(
                    wheel_1__isnull=True, wheel_2__isnull=True,
                    wheel_3__isnull=True).exclude(wheel_1__exact='', wheel_2__exact='', wheel_3__exact='').values(
                    "uuid", "t_name").distinct()
            # print(len(uuid_data))
            tool_types = TbCTypeHead.objects.values("c_type")  # 查出刀具類型
            brand_sorts = TbBrand.objects.values("w_brand")  # 品牌類型
            for i in brand_sorts:
                content["payload"]["axisData"].append(i["w_brand"])
            for tool in tool_types:
                tmp_dict = {"name": '', "data": {}}
                tmp_dict["name"] = tool["c_type"]
                t_name_data = TbDesignmessage.objects.filter(t_type=tool["c_type"]).values("t_name").distinct()
                t_name_list = [t["t_name"] for t in t_name_data]

                for wheel in uuid_data:
                    if wheel["t_name"] in t_name_list:
                        me_no_data = TbProductionmessage.objects.filter(uuid=wheel["uuid"], op_time__gte=start,
                                                                        op_time__lte=end).values("me_no", "wheel_1",
                                                                                                 "wheel_2", "wheel_3")
                        me_no_count = TbProductionmessage.objects.filter(uuid=wheel["uuid"], op_time__gte=start,
                                                                         op_time__lte=end).values("me_no",
                                                                                                  "t_no").distinct()
                        me_no = [i["me_no"] for i in me_no_count]
                        total = get_sum(me_no)

                        w_no_list = []  # 臨時存放砂輪序號

                        for no in me_no_data:
                            if no["wheel_1"]:
                                w_no = no["wheel_1"][:1]
                                w_no_list.append(w_no)
                            if no["wheel_2"]:
                                w_no = no["wheel_2"][:1]
                                w_no_list.append(w_no)
                            if no["wheel_3"]:
                                w_no = no["wheel_3"][:1]
                                w_no_list.append(w_no)
                        tmp_w_no_dict = {}
                        for w in w_no_list:
                            if w in tmp_w_no_dict.keys():
                                tmp_w_no_dict[w] += 1
                            else:
                                tmp_w_no_dict[w] = 1
                        for key, value in tmp_w_no_dict.items():
                            w_bran = TbWheelSort.objects.filter(uuid=wheel["uuid"], w_no=key).values("w_bran")
                            if w_bran:
                                if w_bran[0]["w_bran"] in tmp_dict["data"].keys():
                                    tmp_dict["data"][w_bran[0]["w_bran"]]["repair"] += value
                                    # if flag:
                                    tmp_dict["data"][w_bran[0]["w_bran"]]["total"] += total
                                    # flag -= 1
                                else:
                                    tmp_dict["data"][w_bran[0]["w_bran"]] = {}
                                    tmp_dict["data"][w_bran[0]["w_bran"]]["repair"] = value
                                    tmp_dict["data"][w_bran[0]["w_bran"]]["total"] = total
                            else:
                                continue
                # print(tmp_dict)
                for key, value in tmp_dict["data"].items():
                    if value["repair"] and value["total"]:
                        tmp_dict["data"][key] = round(value["total"] / value["repair"], 2)
                content["payload"]["series"].append(tmp_dict)

            tmp_list = []  # 用於存放空尺寸代碼的key
            tmp_series = content["payload"]["series"]
            for i in range(0, len(tmp_series)):
                if len(tmp_series[i]["data"]) == 0:
                    tmp_list.append(i)
            list1 = []
            for i in range(0, len(tmp_series)):
                if i in tmp_list:
                    pass
                else:
                    list1.append(tmp_series[i])
            tmp_series = list1
            for i in tmp_series:
                for brand in content["payload"]["axisData"]:
                    if brand not in i["data"].keys():
                        i["data"][brand] = 0
            for i in tmp_series:
                tmp_no = []
                for key, value in i["data"].items():
                    for brand in content["payload"]["axisData"]:
                        if key == brand:
                            tmp_no.append(value)
                i["data"] = tmp_no

            content["payload"]["series"] = tmp_series
        except Exception as e:
            content["errmsg"] = str(e)
            content["status"] = 1
        return Response(content)


@api_view(http_method_names=["GET", "POST"])
def detail_compensation_num(request):
    if request.method == "GET":
        content = {"payload": {}, "status": 0, "errmsg": ''}
        try:
            start_time = request.GET.get("start", None)
            end_time = request.GET.get("end", None)
            t_type = request.GET.get("t_type", None)

            if not t_type: raise Exception(u"請傳入刀具類型")

            if not start_time or not end_time:
                end_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M')
                start_time = (datetime.datetime.now() + datetime.timedelta(days=-7)).strftime('%Y-%m-%d %H:%M')

            if end_time < start_time:
                raise Exception(u"開始時間不能大於結束時間")

            tb_c_type = TbCTypeHead.objects.filter(c_type=t_type).last()
            if not tb_c_type: raise Exception(u"查无此款刀具類型")

            c_body = TbCTypeBody.objects.filter(c_type=tb_c_type).values('type_part').distinct()  # 加工部位
            tb_brandmge = TbBrand.objects.values("w_brand")  # 品牌類型

            w_brandList = []
            for br in tb_brandmge:
                w_brandList.append(br['w_brand'])

            uuid_list = []
            content['payload']['series'] = []
            for c in c_body:

                c_body2 = TbCTypeBody.objects.filter(c_type=tb_c_type, type_part=c['type_part']).values(
                    'size_code')  # 查询出加工部位的尺寸代码

                size_list = []
                for c2 in c_body2:
                    size_list.append(c2['size_code'])

                size_list = tuple(size_list)

                sql_uuid = "SELECT DISTINCT(b.uuid) as uuid from \
                          (SELECT DISTINCT(t_name) FROM `tb_designmessage` WHERE t_type='{0}') a ,\
                         (SELECT * FROM `tb_productionmessage` WHERE  (wheel_1!='' or wheel_2!='' or wheel_3!='')  " \
                           "AND uuid is not null AND d_code in {1}  AND date(op_time)>='{2}'  AND  date(op_time)<='{3}')b  WHERE a.t_name=b.t_name".format(
                    t_type, size_list, start_time, end_time)

                data = my_select(sql_uuid)
                if data:
                    for da in data:

                        if da[0] not in uuid_list:
                            uuid_list.append(da[0])
                    uuid_list = list(set(uuid_list))
                    k_list = {}
                    for brl in w_brandList:
                        k_list[brl] = []
                    for u in uuid_list:

                        sql_sum = "SELECT SUM(b.me_no) FROM \
                                  (SELECT t_name,t_ver FROM `tb_designmessage` WHERE t_type='{0}') a ,\
                                  (SELECT DISTINCT(t_no),me_no,t_name,t_ver FROM `tb_productionmessage` WHERE  uuid is not null \
                                   AND date(op_time)>='{1}'  AND date(op_time)<='{2}' \
                                   AND uuid='{3}' ) b\
                                   WHERE a.t_name=b.t_name AND a.t_ver=b.t_ver".format(t_type, start_time, end_time, u)

                        sql_wheel = "SELECT b.wheel_1, b.wheel_2, b.wheel_3 from \
                                   (SELECT * FROM `tb_productionmessage` WHERE  (wheel_1!='' or wheel_2!='' or wheel_3!='')  AND uuid is not null AND date(op_time)>='{0}'  AND date(op_time)<='{1}' AND d_code in {2})b \
                                    WHERE  b.uuid='{3}'".format(start_time, end_time, size_list, u)

                        data1 = my_select(sql_sum)
                        data2 = my_select(sql_wheel)
                        new_num = {}
                        for brl2 in w_brandList:
                            new_num[brl2] = 0

                        if data1 and data2:
                            for d2 in data2:
                                # print(d2)
                                if d2[0]:
                                    tb_wheel_smag = TbWheelSort.objects.filter(uuid=u, w_no=d2[0][0]).values(
                                        'w_bran').last()
                                    if tb_wheel_smag:
                                        new_num[tb_wheel_smag['w_bran']] += 1

                                if d2[1]:
                                    tb_wheel_smag = TbWheelSort.objects.filter(uuid=u, w_no=d2[1][0]).values(
                                        'w_bran').last()
                                    if tb_wheel_smag:
                                        new_num[tb_wheel_smag['w_bran']] += 1

                                if d2[2]:
                                    tb_wheel_smag = TbWheelSort.objects.filter(uuid=u, w_no=d2[2][0]).values(
                                        'w_bran').last()
                                    if tb_wheel_smag:
                                        new_num[tb_wheel_smag['w_bran']] += 1

                            for n in new_num:

                                if int(new_num[n]) != 0:
                                    for d1 in data1:
                                        k1 = int(d1[0]) / int(new_num[n])

                                        k_list[n].append(k1)

                                    if len(k_list[n]) > 1:
                                        avg_a = reduce(lambda x, y: x + y, k_list[n]) / len(k_list[n])

                                        k_list[n].clear()
                                        k_list[n].append(int(avg_a))

                        valuse2 = []
                        for k2 in k_list.values():
                            if k2:
                                for k21 in k2:
                                    if k21 > 0:
                                        valuse2.append(round(k21, 2))

                            else:
                                valuse2.append(0)
                    content['payload']['series'].append({'data': valuse2, 'name': c['type_part']})
            content['payload']['axisData'] = k_list.keys()

        except Exception as e:
            content["status"] = 1
            content["errmsg"] = str(e)
        finally:
            return Response(content)


@api_view(http_method_names=["GET", "POST", "PUT", "DELETE"])
def granularity_compensation(request):
    if request.method == "GET":
        content = {"payload": {}, "status": 0, "errmsg": ''}

        try:
            start_time = request.GET.get("start", None)
            end_time = request.GET.get("end", None)
            t_type = request.GET.get("t_type", None)
            w_gran = request.GET.get("w_gran", None)

            if not t_type: raise Exception(u"請傳入刀具類型")
            if not w_gran: raise Exception(u"請傳入粒度")

            if not start_time or not end_time:
                end_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M')
                start_time = (datetime.datetime.now() + datetime.timedelta(days=-7)).strftime('%Y-%m-%d %H:%M')

            if end_time < start_time:
                raise Exception(u"開始時間不能大於結束時間")

            tb_c_type = TbCTypeHead.objects.filter(c_type=t_type).last()

            if not tb_c_type: raise Exception(u"查无此款刀具類型")

            c_body = TbCTypeBody.objects.filter(c_type=tb_c_type).values('type_part').distinct()  # 加工部位
            tb_brandmge = TbBrand.objects.values("w_brand")  # 品牌類型

            content['payload']['w_gran'] = w_gran
            content['payload']['t_type'] = t_type

            content['payload']['series'] = []

            for b in tb_brandmge:
                valuse2 = []
                content['payload']['axisData'] = []
                type_partList = []
                for c in c_body:

                    type_partList.append(c['type_part'])
                    c_body2 = TbCTypeBody.objects.filter(c_type=tb_c_type, type_part=c['type_part']).values(
                        'size_code')  # 查询出加工部位的尺寸代码
                    size_list = []
                    for c2 in c_body2:
                        size_list.append(c2['size_code'])

                    size_list = tuple(size_list)

                    tb_desi_mge = TbDesignmessage.objects.filter(t_type=t_type).values(
                        't_name').distinct()  # 查询出输入刀具类型的全部刀具名称

                    t_name_list = []
                    for d in tb_desi_mge:
                        t_name_list.append(d['t_name'])

                    t_name_list = tuple(t_name_list)

                    sql_uuid2 = TbWheelSort.objects.filter(t_name__in=t_name_list, w_gran=w_gran, w_bran=b['w_brand'],
                                                           uuid__isnull=False).values('uuid').distinct()

                    uuid_list = []

                    if sql_uuid2:
                        for da in sql_uuid2:
                            uuid_list.append(da['uuid'])

                            uuid_list = list(set(uuid_list))

                    if len(uuid_list) > 0:
                        sum_k = 0
                        uuid_num = 0
                        for u in uuid_list:

                            sql_value = "SELECT b.su/a.w FROM\
                                      (SELECT COUNT(*) as w FROM tb_productionmessage WHERE uuid ='{0}'AND d_code in {1}\
                                      AND (wheel_1!='' or wheel_2!='' or wheel_3!='') AND  date(op_time)>='{2}'  AND date(op_time)<='{3}') a,\
                                      ((SELECT SUM(k.me_no) as su FROM \
                                      (SELECT DISTINCT(t_no),me_no,t_name,t_ver FROM tb_productionmessage WHERE uuid ='{4}' AND d_code in {5} \
                                      AND  date(op_time)>='{6}'  AND date(op_time)<='{7}') k)) b".format(u, size_list,
                                                                                                         start_time,
                                                                                                         end_time, u,
                                                                                                         size_list,
                                                                                                         start_time,
                                                                                                         end_time)

                            data1 = my_select(sql_value)
                            if data1:

                                for d1 in data1:
                                    if d1[0] is None:
                                        pass
                                    else:
                                        sum_k = sum_k + int(d1[0])
                                        uuid_num += 1

                        if uuid_num > 0:
                            gran_num = round(sum_k / uuid_num, 2)
                        else:
                            gran_num = 0
                    else:
                        gran_num = 0
                    valuse2.append(gran_num)

                content['payload']['axisData'] = type_partList
                content['payload']['series'].append({'data': valuse2, 'name': b['w_brand']})

        except Exception as e:
            content["status"] = 1
            content["errmsg"] = str(e)
        finally:
            return Response(content)


@api_view(http_method_names=["GET"])
def granularity_percent(request):
    content = {"payload": {"axisData": [], "series": []}, "errmsg": "", "status": 0}
    if request.method == "GET":
        start = request.GET.get("start", None)
        end = request.GET.get("end", None)
        m_type = request.GET.get("m_type", None)
        t_type = request.GET.get("t_type", None)

        if start is None and end is None:
            start, end = start_end_time()
        else:
            if start > end: raise Exception(u'開始時間不能大於結束時間')
        try:
            t_name_list = []
            if t_type:
                t_name_data = TbDesignmessage.objects.filter(t_type=t_type).values('t_name').distinct()
                t_name_list = [i["t_name"] for i in t_name_data]
            if m_type:
                uuid_data = TbProductionmessage.objects.filter(m_no__contains=m_type, op_time__gte=start,
                                                               op_time__lte=end, status=5).exclude(
                    wheel_1__isnull=True, wheel_2__isnull=True,
                    wheel_3__isnull=True).exclude(wheel_1__exact='', wheel_2__exact='', wheel_3__exact='').values(
                    "uuid", "t_name").distinct()
            else:
                uuid_data = TbProductionmessage.objects.filter(op_time__gte=start, op_time__lte=end,
                                                               uuid__isnull=False, status=5).exclude(
                    wheel_1__isnull=True, wheel_2__isnull=True,
                    wheel_3__isnull=True).exclude(wheel_1__exact='', wheel_2__exact='', wheel_3__exact='').values(
                    "uuid", "t_name").distinct()

            tmp_dict = {}
            for data in uuid_data:
                if t_name_list:
                    if data["t_name"] in t_name_list:
                        product_message = TbProductionmessage.objects.filter(uuid=data["uuid"], op_time__gte=start,
                                                                             op_time__lte=end, status=5).values(
                            "wheel_1", "wheel_2", "wheel_3")
                        me_no_count = TbProductionmessage.objects.filter(uuid=data["uuid"], op_time__gte=start,
                                                                         op_time__lte=end, status=5).values("me_no",
                                                                                                            "t_no").distinct()
                    else:
                        continue
                else:
                    product_message = TbProductionmessage.objects.filter(uuid=data["uuid"], op_time__gte=start,
                                                                         op_time__lte=end, status=5).values("wheel_1",
                                                                                                            "wheel_2",
                                                                                                            "wheel_3")
                    me_no_count = TbProductionmessage.objects.filter(uuid=data["uuid"], op_time__gte=start,
                                                                     op_time__lte=end, status=5).values("me_no",
                                                                                                        "t_no").distinct()
                me_no = [i["me_no"] for i in me_no_count]
                total = get_sum(me_no)

                w_no_list = []  # 臨時存放砂輪序號
                for no in product_message:
                    if no["wheel_1"]:
                        w_no = no["wheel_1"][:1]
                        w_no_list.append(w_no)
                    if no["wheel_2"]:
                        w_no = no["wheel_2"][:1]
                        w_no_list.append(w_no)
                    if no["wheel_3"]:
                        w_no = no["wheel_3"][:1]
                        w_no_list.append(w_no)
                tmp_w_no_dict = {}
                for w in w_no_list:
                    if w in tmp_w_no_dict.keys():
                        tmp_w_no_dict[w] += 1
                    else:
                        tmp_w_no_dict[w] = 1
                for key, value in tmp_w_no_dict.items():
                    w_gran = TbWheelSort.objects.filter(uuid=data["uuid"], w_no=key).values("w_gran")
                    if w_gran:
                        if w_gran[0]["w_gran"] in tmp_dict.keys():
                            tmp_dict[w_gran[0]["w_gran"]]["value"] += value
                            tmp_dict[w_gran[0]["w_gran"]]["total"] += total
                        else:
                            tmp_dict[w_gran[0]["w_gran"]] = {}
                            tmp_dict[w_gran[0]["w_gran"]]["value"] = value
                            tmp_dict[w_gran[0]["w_gran"]]["total"] = total

            for key, value in tmp_dict.items():
                tmp_data = {}
                tmp_data["name"] = key
                tmp_data["value"] = round(value["total"] / value["value"], 2)
                content["payload"]["series"].append(tmp_data)
        except Exception as e:
            content["status"] = 1
            content["errmsg"] = str(e)
        return Response(content)


@api_view(http_method_names=["GET"])
def code_brand_wear(request):
    content = {"payload": {"axisData": [], "series": []}, "errmsg": "", "status": 0}
    if request.method == "GET":
        m_type = request.GET.get("m_type", None)
        t_type = request.GET.get("t_type", None)
        start = request.GET.get("start", None)
        end = request.GET.get("end", None)

        if start is None and end is None:
            start, end = start_end_time()
        else:
            if start > end: raise Exception(u'開始時間不能大於結束時間')
        if t_type is None: raise Exception(u'請選擇刀具類型')

        try:
            if m_type:
                product_data = TbProductionmessage.objects.filter(m_no__icontains=m_type, op_time__gte=start,
                                                                  op_time__lte=end).values("uuid", "d_code", "wheel_1",
                                                                                           "wheel_2", "wheel_3",
                                                                                           "t_name")
            else:
                product_data = TbProductionmessage.objects.filter(op_time__gte=start, op_time__lte=end).values("uuid",
                                                                                                               "d_code",
                                                                                                               "wheel_1",
                                                                                                               "wheel_2",
                                                                                                               "wheel_3",
                                                                                                               "t_name")

            t_name_data = TbDesignmessage.objects.filter(t_type=t_type).values("t_name").distinct()
            brands = TbBrand.objects.values("w_brand")
            for i in brands:
                content["payload"]["axisData"].append(i["w_brand"])

            t_name_list = []
            for i in t_name_data:
                t_name_list.append(i["t_name"])
            tmp_data = {}
            for product in product_data:
                if product["uuid"] and product["t_name"] in t_name_list:
                    if product["d_code"] not in tmp_data.keys():
                        tmp_data[product["d_code"]] = {}
                    w_no_list = []  # 存放砂輪序號
                    wear_data_list = []  # 存放補償值
                    if product["wheel_1"] or product["wheel_2"] or product["wheel_3"]:
                        if product["wheel_1"]:
                            tmp1 = product["wheel_1"].split("+")
                            for i in tmp1:
                                # print(i)
                                if i[3:]:
                                    w_no_list.append(i[:1])
                                    wear_data_list.append(float(i[3:]))

                        if product["wheel_2"]:
                            tmp1 = product["wheel_2"].split("+")
                            for i in tmp1:
                                if i[3:]:
                                    w_no_list.append(i[:1])
                                    wear_data_list.append(float(i[3:]))
                        if product["wheel_3"]:
                            tmp1 = product["wheel_3"].split("+")
                            for i in tmp1:
                                if i[3:]:
                                    w_no_list.append(i[:1])
                                    wear_data_list.append(float(i[3:]))
                    tmp_w_no = {}  # 篩選出砂輪最多的一個
                    if w_no_list:
                        for i in range(0, len(w_no_list)):
                            if w_no_list[i] in tmp_w_no.keys():
                                tmp_w_no[w_no_list[i]] += 1
                            else:
                                tmp_w_no[w_no_list[i]] = 1
                    max_no = ''
                    if tmp_w_no:
                        max_no = max(tmp_w_no, key=tmp_w_no.get)
                    if max_no:
                        brand = TbWheelSort.objects.filter(uuid=product["uuid"], w_no=max_no).values("w_bran")
                        if brand:
                            if brand[0]["w_bran"] not in tmp_data[product["d_code"]].keys():
                                tmp_count = 0
                                for i in range(0, len(w_no_list)):
                                    if w_no_list[i] == max_no:
                                        tmp_count += wear_data_list[i]
                                tmp_data[product["d_code"]][brand[0]["w_bran"]] = []
                                tmp_data[product["d_code"]][brand[0]["w_bran"]].append(tmp_count)
                            else:
                                tmp_count = 0
                                for i in range(0, len(w_no_list)):
                                    if w_no_list[i] == max_no:
                                        tmp_count += wear_data_list[i]
                                tmp_data[product["d_code"]][brand[0]["w_bran"]].append(tmp_count)

            for key, value in tmp_data.items():
                for key1, value1 in value.items():
                    count = 0
                    for i in value1:
                        count += float(i)
                    value[key1] = abs(count) / len(value1)

            tmp_list = []  # 用於存放空尺寸代碼的key
            for key, value in tmp_data.items():
                if len(value) == 0:
                    tmp_list.append(key)

            for i in tmp_list:
                tmp_data.pop(i)

            for key, value in tmp_data.items():
                l = []
                for key1, value1 in value.items():
                    l.append(key1)
                for i in content["payload"]["axisData"]:
                    if i not in l:
                        value[i] = 0

            for key, value in tmp_data.items():  # 改成表格格式
                tmp_dict = {}
                tmp_list1 = []
                tmp_list2 = []
                tmp_dict["name"] = key
                for key1, value1 in value.items():
                    tmp_list1.append(key1)
                tmp_list1 = sorted(tmp_list1, key=(content["payload"]["axisData"]).index)
                for tmp_list in tmp_list1:
                    tmp_list2.append(abs(round(value[tmp_list], 3)))
                tmp_dict["data"] = tmp_list2

                content["payload"]["series"].append(tmp_dict)

        except Exception as e:
            content["status"] = 1
            content["errmsg"] = str(e)
            pass
        return Response(content)


@api_view(http_method_names=["GET", "POST"])
def smart_adjust(request):
    content = {"payload": "", "status": 0, "errmsg": ""}
    url = request.POST.get("url", None)
    try:
        r = requests.get(url=url)
        file_path = os.path.join(settings.MEDIA_ROOT, 'adjust_file.txt')
        with open(file_path, 'wb') as f:  # 下載文件
            f.write(r.content)
        file_info = SocketTranslateFile("10.142.220.158", settings.FILE_TRANSLATE_PORT, settings.RECV_BUFFER,
                                        file_path).adjust()
        if file_info == "adjust_finish":
            pass
        else:
            content["errmsg"] = file_info
            content["status"] = 1
    except Exception as e:
        content["status"] = 1
        content["errmsg"] = str(e)
    return Response(content)


@api_view(http_method_names=["GET"])
def get_amachine_type(request):
    content = {"payload": [], "status": 0, "errmsg": ""}
    if request.method == "GET":
        start = request.GET.get("start", None)
        end = request.GET.get("end", None)
        try:
            m_list = []
            all_m_type = TbMachine.objects.values("m_type").distinct()
            for i in all_m_type:
                wheel_data = TbWheelSort.objects.filter(m_no__icontains=i["m_type"], update_time__gte=start,
                                                        update_time__lte=end).first()
                if wheel_data:
                    m_list.append(i["m_type"])
                else:
                    continue
            content["payload"] = m_list
        except Exception as e:
            content["status"] = 1
            content["errmsg"] = str(e)
        return Response(content)


@api_view(http_method_names=["GET"])
def get_bmachine_type(request):
    content = {"payload": {}, "status": 0, "errmsg": ""}
    if request.method == "GET":
        start = request.GET.get("start", None)
        end = request.GET.get("end", None)
        m_type = request.GET.get("m_type", None)
        try:
            if m_type is None:
                m_list = []
                all_m_type = TbMachine.objects.values("m_type").distinct()
                for i in all_m_type:
                    product_data = TbProductionmessage.objects.filter(m_no__icontains=i["m_type"], op_time__gte=start,
                                                                      op_time__lte=end).first()
                    if product_data:
                        m_list.append(i["m_type"])
                    else:
                        continue
                    content["payload"]["m_type"] = m_list
            else:
                t_list = []
                all_t_name = TbProductionmessage.objects.filter(m_no__icontains=m_type, op_time__gte=start,
                                                                op_time__lte=end).values("t_name").distinct()
                for i in all_t_name:
                    t_type = TbDesignmessage.objects.filter(t_name=i["t_name"]).values("t_type").first()
                    t_list.append(t_type["t_type"])
                t_list = list(set(t_list))
                content["payload"]["t_type"] = t_list

        except Exception as e:
            content["status"] = 1
            content["errmsg"] = str(e)
        return Response(content)
